sql - count sequence of particular value

Question

I have a snowflake table like this:

date_col | artc | locl | flag
d1 | a1 | l1 | 0         
d2 | a1 | l1 | 1         
d3 | a1 | l1 | 1         
d4 | a1 | l1 | 0         
d5 | a1 | l1 | 0         
d6 | a1 | l1 | 1         
d7 | a1 | l1 | 0         
d8 | a1 | l1 | 1         
d9 | a1 | l1 | 1         
d10 | a1 | l1 | 1         
d1 | a1 | l2 | 0         
d2 | a1 | l2 | 1         
d3 | a1 | l2 | 1         
d4 | a1 | l2 | 1         
d5 | a1 | l2 | 1         
d6 | a1 | l2 | 1         
d7 | a1 | l2 | 0         
d8 | a1 | l2 | 1         
d9 | a1 | l2 | 1         
d10 | a1 | l2 | 1    

and I want to count sequence of 1 on column "flag" group by artc and locl, order by date_col

The desire output is:

flag
2
1
3
5
3

I was trying things like:

select 
count(*) Over(PARTITION BY art, locl, flag
ORDER BY date_col rows 
unbounded preceding) as flag
from table
where flag=1

but it doesn't start fresh count on group changes. Thanks for any help!

question from:https://stackoverflow.com/questions/65910124/count-sequence-of-particular-value

Answer 1

Assign each group of "1"s a grouping by counting the number of "0"s before them. Then filter and aggregate:

select count(*), min(date_col), max(date_col)
from (select t.*,
             sum(case when flag = 0 then 1 else 0 end) over (order by date_col) as grp
      from t
     ) t
where flag = 1
group by grp
order by min(date)