lesson 76 Warehouse & reporting projects · advanced

Monthly change & cumulative reports

Measure monthly change and cumulative totals.

the idea

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.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

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;

Watch for: The first month has no prior observation; zero change would misrepresent that absence.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Establish monthly grain. The window should operate on these three monthly rows.

   step-1.sql Run

   sql›

   SELECT strftime('%Y-%m',order_date) AS month,SUM(sales) AS sales
   FROM fact_sales
   GROUP BY month
   ORDER BY month;

2. 2

   Accumulate revenue. An explicit ROWS frame includes all observed prior months.

   step-2.sql Run

   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
   ORDER BY month

3. 3

   Compare prior observations. LAG returns NULL for the first month.

   step-3.sql Run

   sql›

   WITH monthly AS(
     SELECT strftime('%Y-%m',order_date) AS month,SUM(sales) AS sales
     FROM fact_sales
     GROUP BY month
   ),compared AS(
     SELECT *,LAG(sales) OVER(ORDER BY month) AS previous_sales
     FROM monthly
   )
   SELECT
     month,
     sales,
     previous_sales,
     ROUND(100.0*(sales-previous_sales)/NULLIF(previous_sales,0),2) AS change_pct
   FROM compared
   ORDER BY month