milestone 5/6 Monthly change & cumulative reports

Measure monthly change and cumulative totals.

Warehouse and Reporting · Profile raw CRM/sales records, build Silver and Gold tables in the disposable lab, explore the fact table, and publish repeatable reporting logic.

approach

Aggregate to monthly grain before using LAG or a running SUM. A percentage change needs a nonzero prior value. This report uses observed months; a complete calendar would be required if missing months should appear as zero.

worked-example.sql

WITH monthly AS(
  SELECT strftime('%Y-%m',order_date) AS month, SUM(sales) AS sales
  FROM fact_sales
  GROUP BY month
)
SELECT
  month,
  sales,
  SUM(sales) OVER(ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_sales
FROM monthly;

data for this milestone

fact_sales6 columns · 9 rows

sale_keyinteger · key	customer_keyinteger	product_keyinteger	quantityinteger	salesreal	order_datetext
1	1	1	2	24	2025-01-01
2	2	3	1	80	2025-01-02
3	1	2	1	45	2025-01-15
4	3	4	2	240	2025-02-01
5	4	3	2	160	2025-02-05

showing 5 of 9 rows