Monthly GMV Against the Previous Calendar Month
Finance reports GMV — the sum of order_total over orders that are not cancelled — for each month that had such orders, next to the previous calendar month.
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #112
Problem statement
Finance reports GMV — the sum of order_total over orders that are not cancelled — for each month that had such orders, next to the previous calendar month. A month that had no orders produces no row, and it also means the month after it has no previous figure.
Return month (YYYY-MM), gmv, prev_month_gmv (the GMV of the calendar month just before, or NULL if that month has no row) and change (gmv - prev_month_gmv, NULL when there is no previous figure), ordered by month.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| placed_at | datetime |
| order_total | int |
| status | enum(placed, packed, shipped, delivered, cancelled) |
Primary key: order_id.
One row per order; order_total in rupees.
Examples
Example 1
orders
| order_id | placed_at | order_total | status |
|---|---|---|---|
| 1 | 2024-01-05 10:00:00 | 1200 | delivered |
| 2 | 2024-01-20 19:30:00 | 800 | delivered |
| 3 | 2024-02-02 08:15:00 | 2500 | shipped |
| 4 | 2024-02-14 21:00:00 | 999 | cancelled |
| 5 | 2024-04-01 12:00:00 | 3100 | delivered |
| 6 | 2024-05-11 16:45:00 | 1700 | placed |
| 7 | 2024-05-30 23:59:00 | 600 | delivered |
Output
| month | gmv | prev_month_gmv | change |
|---|---|---|---|
| 2024-01 | 2000 | NULL | NULL |
| 2024-02 | 2500 | 2000 | 500 |
| 2024-04 | 3100 | NULL | NULL |
| 2024-05 | 2300 | 3100 | -800 |
How to solve Monthly GMV Against the Previous Calendar Month
Start with one row per month: GMV is a grouped SUM of the non-cancelled orders, keyed by DATE_FORMAT(placed_at, '%Y-%m').
The tempting next step is LAG(gmv) OVER (ORDER BY month), but LAG looks at the previous row. In the example March has no orders, so April's previous row is February — and comparing April with February is exactly what the statement forbids. The fix is to lag the month label too and keep the lagged value only when that label equals the month one calendar month before (DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH) formatted back to YYYY-MM); otherwise both prev_month_gmv and change are NULL.
The self-join alternative expresses the rule directly: LEFT JOIN each month to the row whose label is one month earlier. A missing month simply finds no partner, and m.gmv - NULL is NULL. change is a reserved word in MySQL, so the alias is written in backticks. Both versions aggregate once and then touch one row per month.
Reference solution (MySQL)
WITH monthly AS (
SELECT DATE_FORMAT(placed_at, '%Y-%m') AS month, SUM(order_total) AS gmv
FROM orders
WHERE status <> 'cancelled'
GROUP BY DATE_FORMAT(placed_at, '%Y-%m')
), lagged AS (
SELECT month, gmv,
LAG(month) OVER (ORDER BY month) AS prev_month,
LAG(gmv) OVER (ORDER BY month) AS prev_gmv
FROM monthly
)
SELECT month, gmv,
CASE WHEN prev_month = DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') THEN prev_gmv END AS prev_month_gmv,
CASE WHEN prev_month = DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') THEN gmv - prev_gmv END AS `change`
FROM lagged
ORDER BY monthAnother way
WITH monthly AS (
SELECT LEFT(placed_at, 7) AS month, SUM(order_total) AS gmv
FROM orders WHERE status <> 'cancelled' GROUP BY LEFT(placed_at, 7)
)
SELECT m.month, m.gmv, p.gmv AS prev_month_gmv, m.gmv - p.gmv AS `change`
FROM monthly m
LEFT JOIN monthly p ON p.month = LEFT(DATE_SUB(CONCAT(m.month, '-01'), INTERVAL 1 MONTH), 7)
ORDER BY m.month← Flash Sale Units Sold Inside the Sale Window · Customers Who Reordered Within 30 Days →