The Relational Thinking - Technique #1 - Inner Join

for_loops_article-code_2.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
)
   , 
/* THE ACTUAL CODE */
monthly_mtd_window AS (
    SELECT dt pivot_date, date_trunc(MONTH, dt) dt_from, dt dt_to
    FROM fake_dates
    WHERE dt < '2020-12-13'::date
)
SELECT pivot_date as date, sum(sales.revenue) as revenue_mtd
FROM fake_sales
INNER JOIN monthly_mtd_window 
    ON sales.dt BETWEEN dt_from and dt_to
group by pivot_date