lesson 76 Warehouse & reporting projects · advanced

Monthly change & cumulative reports

Measure monthly change and cumulative totals.

the idea

Monthly reports aggregate to one row per month first, then use window functions across months: SUM(…) OVER (ORDER BY month) for cumulative sales and LAG(…) for the previous month’s value and the percentage change.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Aggregate sales by month first. One row per month with rounded totals—that monthly grain is what running sums and month-over-month windows need.the pattern

   example.sql Run

   sql›

   SELECT substr(order_date, 1, 7) AS month, ROUND(SUM(sales), 2) AS sales
   FROM fact_sales
   GROUP BY month
   ORDER BY month;

2. 2

   Find the busiest months by orders. Distinct order_id per month shows which months drove the most orders—November usually leads for this wholesaler.

   step-2.sql Run

   sql›

   SELECT
     substr(order_date, 1, 7) AS month,
     COUNT(DISTINCT order_id) AS orders
   FROM fact_sales
   GROUP BY month
   ORDER BY orders DESC
   LIMIT 3;

3. 3

   Compare each month to the prior one. After aggregating to months, LAG(orders) pulls the previous month’s value on the same result set.

   step-3.sql Run

   sql›

   WITH m AS (
     SELECT
       substr(order_date, 1, 7) AS month,
       COUNT(DISTINCT order_id) AS orders
     FROM fact_sales
     GROUP BY month
   )
   SELECT
     month,
     orders,
     LAG(orders) OVER (ORDER BY month) AS previous_orders
   FROM m
   ORDER BY month;

common mistake

Applying LAG before aggregating compares sales lines, not months.