Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions

Question

Summary

I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views.

• The counter should increase for each consecutive day that the client's slug doesn't receive a view.
• If/once the slug gets a view, then the counter should restart.

I can figure out a regular running total, but I can't seem to figure out how to make it restart. I've tried several ways with aggregate/window functions, but I can't seem to achieve the desired results.

---

Source data dcev_zero_views_flag

client_name	slug	calendar_date	views	consecutive_zero_views_flag
Ambition LLC	canyon_properties	2022-02-16T00:00:00.000Z	2	0
Ambition LLC	canyon_properties	2022-02-17T00:00:00.000Z	0	0
Ambition LLC	canyon_properties	2022-02-18T00:00:00.000Z	0	1
Ambition LLC	canyon_properties	2022-02-19T00:00:00.000Z	0	1
Ambition LLC	canyon_properties	2022-02-20T00:00:00.000Z	2	0
Ambition LLC	canyon_properties	2022-02-21T00:00:00.000Z	0	0
Ambition LLC	canyon_properties	2022-02-22T00:00:00.000Z	0	1
Ambition LLC	canyon_properties	2022-02-23T00:00:00.000Z	1	0
Ambition LLC	city_station_apartment_homes	2022-02-16T00:00:00.000Z	0	0
Ambition LLC	city_station_apartment_homes	2022-02-17T00:00:00.000Z	0	1
Ambition LLC	city_station_apartment_homes	2022-02-18T00:00:00.000Z	3	0
Ambition LLC	city_station_apartment_homes	2022-02-19T00:00:00.000Z	2	0
Ambition LLC	city_station_apartment_homes	2022-02-20T00:00:00.000Z	0	0
Ambition LLC	city_station_apartment_homes	2022-02-21T00:00:00.000Z	0	1
Ambition LLC	city_station_apartment_homes	2022-02-22T00:00:00.000Z	0	1
Ambition LLC	city_station_apartment_homes	2022-02-23T00:00:00.000Z	2	0
D&D Properties Inc	east_side_living_spaces	2022-02-16T00:00:00.000Z	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-17T00:00:00.000Z	1	0
D&D Properties Inc	east_side_living_spaces	2022-02-18T00:00:00.000Z	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-19T00:00:00.000Z	1	0
D&D Properties Inc	east_side_living_spaces	2022-02-20T00:00:00.000Z	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-21T00:00:00.000Z	1	0
D&D Properties Inc	east_side_living_spaces	2022-02-22T00:00:00.000Z	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-23T00:00:00.000Z	0	1
D&D Properties Inc	happy_mountain_homes	2022-02-16T00:00:00.000Z	1	0
D&D Properties Inc	happy_mountain_homes	2022-02-17T00:00:00.000Z	0	0
D&D Properties Inc	happy_mountain_homes	2022-02-18T00:00:00.000Z	0	1
D&D Properties Inc	happy_mountain_homes	2022-02-19T00:00:00.000Z	0	1
D&D Properties Inc	happy_mountain_homes	2022-02-20T00:00:00.000Z	0	1
D&D Properties Inc	happy_mountain_homes	2022-02-21T00:00:00.000Z	0	1
D&D Properties Inc	happy_mountain_homes	2022-02-22T00:00:00.000Z	1	0
D&D Properties Inc	happy_mountain_homes	2022-02-23T00:00:00.000Z	0	0

For reference, the consecutive_zero_views_flag column in returns 1 if the view count for the current and preceding day = 0. Otherwise, it returns 0 . It's derived by:

case when 
  dcev.views = 0 
  and dcev.views = lag(dcev.views) over (PARTITION by client_name, slug ORDER BY dcev.calendar_date) 
then 1 
else 0 
end as consecutive_zero_views_flag

---

Desired output

I want to add a column, consecutive_days_without_views. That should represent a running total of the consecutive_zero_views_flag for each (client_name slug) combination ordered by date. But, the running total should restart any time consecutive_zero_views_flag changes from 1 to 0 for each client_name/slug combination, in order of date.

Example of desired output

Note the last row of the example below that shows the 2 for consecutive_days_without_views.

client_name	slug	calendar_date	views	consecutive_zero_views_flag	consecutive_days_without_views
Ambition LLC	canyon_properties	2022-02-16T00:00:00.000Z	2	0	0
Ambition LLC	canyon_properties	2022-02-17T00:00:00.000Z	0	0	0
Ambition LLC	canyon_properties	2022-02-18T00:00:00.000Z	0	1	1
Ambition LLC	canyon_properties	2022-02-19T00:00:00.000Z	0	1	2
Ambition LLC	canyon_properties	2022-02-20T00:00:00.000Z	2	0	0
Ambition LLC	canyon_properties	2022-02-21T00:00:00.000Z	0	0	0
Ambition LLC	canyon_properties	2022-02-22T00:00:00.000Z	0	1	1
Ambition LLC	canyon_properties	2022-02-23T00:00:00.000Z	1	0	0
Ambition LLC	city_station_apartment_homes	2022-02-16T00:00:00.000Z	0	0	0
Ambition LLC	city_station_apartment_homes	2022-02-17T00:00:00.000Z	0	1	1
Ambition LLC	city_station_apartment_homes	2022-02-18T00:00:00.000Z	3	0	0
Ambition LLC	city_station_apartment_homes	2022-02-19T00:00:00.000Z	2	0	0
Ambition LLC	city_station_apartment_homes	2022-02-20T00:00:00.000Z	0	0	0
Ambition LLC	city_station_apartment_homes	2022-02-21T00:00:00.000Z	0	1	1
Ambition LLC	city_station_apartment_homes	2022-02-22T00:00:00.000Z	0	1	2
Ambition LLC	city_station_apartment_homes	2022-02-23T00:00:00.000Z	2	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-16T00:00:00.000Z	0	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-17T00:00:00.000Z	1	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-18T00:00:00.000Z	0	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-19T00:00:00.000Z	1	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-20T00:00:00.000Z	0	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-21T00:00:00.000Z	1	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-22T00:00:00.000Z	0	0	0
D&D Properties Inc	east_side_living_spaces	2022-02-23T00:00:00.000Z	0	1	1
D&D Properties Inc	happy_mountain_homes	2022-02-16T00:00:00.000Z	1	0	0
D&D Properties Inc	happy_mountain_homes	2022-02-17T00:00:00.000Z	0	0	0
D&D Properties Inc	happy_mountain_homes	2022-02-18T00:00:00.000Z	0	1	1
D&D Properties Inc	happy_mountain_homes	2022-02-19T00:00:00.000Z	0	1	2
D&D Properties Inc	happy_mountain_homes	2022-02-20T00:00:00.000Z	0	1	3
D&D Properties Inc	happy_mountain_homes	2022-02-21T00:00:00.000Z	0	1	4
D&D Properties Inc	happy_mountain_homes	2022-02-22T00:00:00.000Z	1	0	0
D&D Properties Inc	happy_mountain_homes	2022-02-23T00:00:00.000Z	0	0	0

Answer 1

Discovered the answer. It's a fairly classic gaps & islands problem.

CTE

• Generate row id overall
• Generate id for the zero flag combination
• Generate id based on differential of first two ids

Excerpt

, ROW_NUMBER() over (order by client_name asc, slug asc, calendar_date asc) id_overall
    , ROW_NUMBER() over (partition by client_name, slug, consecutive_zero_views_flag order by client_name asc, slug asc, calendar_date asc) id_zero_flag
    , ((ROW_NUMBER() over (order by client_name asc, slug asc, calendar_date asc))-(ROW_NUMBER() over (partition by client_name, slug, consecutive_zero_views_flag order by client_name asc, slug asc, calendar_date asc))) id_diff_overall_zero_flag

Then, the query

• Add case when consecutive zero flag is 1, return row number that's partitioned by the differential ID

Excerpt

case 
     when consecutive_zero_views_flag=1 
        then ROW_NUMBER() over (partition by id_diff_overall_zero_flag order by client_name asc, slug asc, calendar_date asc)
     else 0 
     end as consecutive_days_without_views

I referenced numerous sources to get to this solution. Here are a few, in no particular order, in case you have a similar challenge to solve:

• Solving "Gaps and Islands" with row_number() and dense_rank()?
• https://spin.atomicobject.com/2021/09/27/gaps-islands-problem-postgres/
• https://bertwagner.com/posts/gaps-and-islands/