Rolling 30 Day Average (Lesson 6) | Leverage the Rows Between Syntax

rolling_average.sql

-- determine the start and end dates of the dataset
with cte_strt_end_dates as (
    select min(order_date) as start_date,
        max(order_date) as end_date,
        (end_date - start_date)::int as num_days
    from public.subscriptions
),
-- generate a dummy dataset that should hold a pro forma row for each day between the start date and end date
cte_dummy_tbl as (
    SELECT (select start_date from cte_strt_end_dates) as start_date,
        (select end_date from cte_strt_end_dates) as end_date,
        0 as dft_rev,
        0 as sale_rpt_today
    FROM TABLE(GENERATOR(ROWCOUNT => 10000))
),
-- narrow down the dummy dataset to the exact number of days (rows)
cte_dft_rev_tbl as (
    select *,
        (row_number() over (order by start_date)) - 1 as row_num,
        start_date + row_num as row_date
    from cte_dummy_tbl
    qualify row_num <= (select num_days from cte_strt_end_dates)
),
-- generate the daily revenue by order date
cte_daily_rev as (
    select sub.order_date, 
        sum(sub.revenue) as subs_daily_rev,
        1 as sale_rpt_today
    from subscriptions sub
    group by sub.order_date
)
-- calculate the average total sales on running 30 day basis 
select start_date,
    end_date,
    row_date,
    coalesce(act.subs_daily_rev, dft.dft_rev) as daily_rev,
    coalesce(act.sale_rpt_today, dft.sale_rpt_today) as sale_today,
    sum(daily_rev) over (order by row_date rows between 29 preceding and current row) as tot_sales_lst_30_days,
    sum(sale_today) over (order by row_date rows between 29 preceding and current row) as num_days_with_sale_lst_30_days,
    case when num_days_with_sale_lst_30_days > 0 then round((tot_sales_lst_30_days / num_days_with_sale_lst_30_days), 2) else 0 end as avg_sales_lst_30_days
from cte_dft_rev_tbl dft
left join cte_daily_rev act on dft.row_date = act.order_date
order by row_date