SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #tmpTable

CREATE TABLE #tmpTable 
(
    mooniq int,
    testresult int,
    series bigint
)

INSERT INTO #tmpTable (mooniq, testresult, series) 
VALUES
(74904, NULL, 1),
(74904, NULL, 2),
(74904, NULL, 3),
(74904, NULL, 4),
(74904, 1, 5),
(74904, 1, 6),
(74904, 1, 7),
(74904, NULL, 8),
(74904, NULL, 9),
(94904, NULL, 1),
(94904, NULL, 2),
(94904, NULL, 3),
(94904, NULL, 4),
(94904, 1, 5),
(94904, 1, 6),
(94904, 1, 7),
(94904, 1, 8),
(94904, 1, 9),
(24904, NULL, 1),
(24904, NULL, 2),
(24904, NULL, 3),
(24904, NULL, 4),
(24904, 1, 5),
(24904, NULL, 6),
(24904, 1, 7),
(24904, NULL, 8),
(24904, 1, 9);
27 rows affected

with cte as (
select *,
       case when t1.testresult = 1 then 
       (
       select max(t2.series)
         from #tmpTable t2
        where t1.mooniq = t2.mooniq
          and t1.series > t2.series
          and t2.testresult is null
       ) end as last_fail_series_from_a_pass
  from #tmpTable t1
)
select mooniq,
       nullif(max(series - last_fail_series_from_a_pass),1) as continous_fail
  from cte
 where testresult = 1
 group
    by mooniq


mooniq
continous_fail


24904
null

74904
3

94904
5


with grouped_moons as (
 select mooniq, 
        testresult,
        series,
        dense_rank() over (partition by mooniq order by series) -
        dense_rank() over (partition by mooniq, testresult order by series) mt_seq 
   from #tmpTable t)
select distinct
       mooniq, 
       max(nullif(count(*),1)) over (partition by mooniq) as continous_fail
  from grouped_moons 
 where testresult = 1
 group 
    by mooniq, 
       mt_seq


mooniq
continous_fail


24904
null

74904
3

94904
5


Warning: Null value is eliminated by an aggregate or other SET operation.


with grouped_moons as (
 select mooniq, 
        testresult,
        series,
        dense_rank() over (partition by mooniq order by series) -
        dense_rank() over (partition by mooniq, testresult order by series) mt_seq 
   from #tmpTable t)
select mooniq, 
       mt_seq,
       nullif(count(*),1) as continous_fail
  from grouped_moons 
 where testresult = 1
 group 
    by mooniq, 
       mt_seq


mooniq
mt_seq
continous_fail


24904
4
null

74904
4
3

94904
4
5

24904
5
null

24904
6
null