Count contiguous occurrences of the same value in a table

Question

Using SQL Server 2019 Standard edition. I am collecting moon measurements from the moons and need to detect the number of times a test has failed contiguously over a nine intervals (or series). A numeral one indicates a failure.

There's test data here in this table, and my expected output query is further below. Very important to understand contiguously here. If a test fails (1 is reported) on 5,7 and 9 but not on 6 and 8 then it is not counted. Implicitly, 1 is, therefor, not a valid result.

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);

I'm trying to get the query's output to match this query's output:

--- The results should look like this query's results
/*
mooniq  contiqs
------  -------
24904   0  
74904   3
94904   5
*/
SELECT 'mooniq' = 74904, 'contiqs' = 3
UNION
SELECT 'mooniq' = 94904 , 'contigs' = 5
UNION
SELECT 'mooniq' = 24904 , 'contigs' = 0

So far I've tried the approach laid out here and am not successful with the ornery mooniq '24904' because although it has 3 failures, none of those are contiguous, and so should not be counted.

--An ambiguation of a business problem simplified for you
--So far I've tried:
with grouped_moons as (
 select mooniq, 
          testresult,
          dense_rank() over (order BY mooniq,series) drom ,
          dense_rank() over (partition by testresult order by mooniq, series)  DRPQ 
                  from #tmpTable t)
select * from grouped_moons order by mooniq

Answer 1

As far as getting the method you were using working, you have two additional problems to solve:

1. You could get multiple groups per mooniq (as opposed to the example which only has one).
2. You need to convert 1 to 0 (mostly trivial)

So you need to use an additional sub-query / CTE to find the max of the groups within a mooniq e.g.

with cte1 as (
  select mooniq, count(*) numrows
  from (
    select t.*
      , row_number() over (partition by mooniq order by series) -
        row_number() over (partition by mooniq, testresult order by series)
      as grp
    from #tmpTable t
  ) t
  where testresult = 1
  group by grp, mooniq
)
select mooniq, max(case when numrows = 1 then 0 else numrows end)
from cte1
group by mooniq;