Relational Thinking - Technique #2 - Windowed Function

for_loops_article-code_5.sql

/* FABRICATE SOME EXAMPLES */
WITH fake_sales              AS (
    select '2020-12-01'::date dt, 100.00 revenue union all
    select '2020-12-02'::date dt, 200.00 revenue union all
    select '2020-12-08'::date dt, 300.00 revenue union all
    select '2020-12-09'::date dt, 400.00 revenue union all
    select '2020-12-10'::date dt, 500.00 revenue
)
   , fake_dates              AS (
    SELECT '2020-12-01'::date + SEQ4() dt
    FROM TABLE (GENERATOR(ROWCOUNT => 31)) v
)

SELECT fake_dates.dt as date,
    sum(sales.revenue) over (order by fake_dates.dt 
                             ROWS BETWEEN UNBOUNDED PRECEDING 
                                      AND CURRENT ROW)   as revenue_mtd
FROM fake_dates
LEFT JOIN fake_sales sales
    ON sales.dt = fake_dates.dt