In pseudo code,
group_index is defined as:
case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?
If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right?
Something like this?
WITH vals (i,v) AS (VALUES (0,1),(1,0),(2,0),(3,1),(4,0),(5,0),(6,1),(7,1),(8,0),(9,1)),
grouped_vals AS (SELECT *,
case when LAG(v) OVER (ORDER BY i) = v then lag(i) OVER (ORDER BY i) ELSE i END AS group_index
FROM vals
)
select *, row_number() OVER (PARTITION BY group_index ORDER BY i)
from grouped_vals
where v = 0;
