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



  • 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


  • 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:

    • https://dba.stackexchange.com/questions/167068/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/



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2