1

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

1 Answer 1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.