How to write a query to attach rownumber(1 to n) to each records for each group

Question

I have a dataset something like below

|date|flag|
|20190503|0|
|20190504|1|
|20190505|1|
|20190506|1|
|20190507|1|
|20190508|0|
|20190509|0|
|20190510|0|
|20190511|1|
|20190512|1|
|20190513|0|
|20190514|0|
|20190515|1|

What I want to achieve is to group the consecutive dates by flag=1, and add one column counter to mark 1 for the first day of the consecutive days where flag=1, and 2 for the 2nd day and etc, assign 0 for flag=0

|date|flag|counter|
|20190503|0|0|
|20190504|1|1|
|20190505|1|2|
|20190506|1|3|
|20190507|1|4|
|20190508|0|0|
|20190509|0|0|
|20190510|0|0|
|20190511|1|1|
|20190512|1|2|
|20190513|0|0|
|20190514|0|0|
|20190515|1|1|

I tried analytical function and hierarchy query, but still haven't found a solution, seeking help, any hint is appreciated!

Thanks, Hong

Answer 1

You can define the groups using a cumulative sum of the zeros. Then use row_number():

select t.*,
       (case when flag = 0 then 0
             else row_number() over (partition by grp order by date)
        end) as counter
from (select t.*,
             sum(case when flag = 0 then 1 else 0 end) over (order by date) as grp
      from t
     ) t;

A very different approach is to take the difference between the current date and a cumulative max of the flag = 0 date:

select t.*,
       datediff(day,
                max(case when flag = 0 then date end) over (order by date),
                date
               ) as counter
from t;

Note that the logic of these two approaches is different -- although they should produce the same results for the data you have provided. For missing dates, the first just ignores missing dates. The second will increment the counter for missing dates.