Advanced
Case 37 of 50
Case 037: Month-over-Month
orderspayments
Leadership already has the monthly revenue numbers from before — but a single month's total on its own doesn't say whether the business is actually heading up or down.
The manager wants that comparison built in: "Revenue by month is good, but I want to see the change — are we growing or shrinking, month to month?"
The data Alex is looking at
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Show total revenue per month, along with the change from the previous month.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH monthly AS (
SELECT
DATE_FORMAT(orders.order_date, '%Y-%m') AS month,
SUM(payments.amount) AS revenue
FROM orders
JOIN payments ON payments.order_id = orders.id
GROUP BY month
)
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change_from_prev_month
FROM monthly
ORDER BY month;
What that query returns
Case Debrief
LAG over an aggregated CTE (time-series comparison)
- Window functions work just as well over an aggregated CTE as over raw rows — this is the standard pattern for any "compared to last period" report.