Month-Over-Month Delivered GMV Change by City — SQL Problem
The monthly business review shows each city's delivered GMV (sum of order_value of delivered orders) per calendar month, next to the city's figure for its…
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #161
Problem statement
The monthly business review shows each city's delivered GMV (sum of order_value of delivered orders) per calendar month, next to the city's figure for its previous month with delivered orders. Cancelled orders are not GMV, and a month in which a city delivered nothing has no row.
Return city, month (as YYYY-MM), gmv, prev_gmv and gmv_change (gmv − prev_gmv). For a city's first month both prev_gmv and gmv_change are NULL. Order the rows by city, then month.
Tables
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| city | varchar |
| placed_at | datetime |
| order_value | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order, value in rupees; city is the delivery city.
Examples
Example 1
Orders
| order_id | city | placed_at | order_value | status |
|---|---|---|---|---|
| 1 | Pune | 2024-01-05 13:00:00 | 400 | delivered |
| 2 | Pune | 2024-01-20 20:00:00 | 350 | delivered |
| 3 | Pune | 2024-02-11 19:30:00 | 900 | delivered |
| 4 | Pune | 2024-02-12 21:00:00 | 500 | cancelled |
| 5 | Pune | 2024-04-02 12:40:00 | 620 | delivered |
| 6 | Kochi | 2024-01-31 23:50:00 | 280 | delivered |
| 7 | Kochi | 2024-02-01 00:10:00 | 260 | delivered |
| 8 | Kochi | 2024-03-15 13:15:00 | 300 | cancelled |
Output
| city | month | gmv | prev_gmv | gmv_change |
|---|---|---|---|---|
| Kochi | 2024-01 | 280 | NULL | NULL |
| Kochi | 2024-02 | 260 | 280 | -20 |
| Pune | 2024-01 | 750 | NULL | NULL |
| Pune | 2024-02 | 900 | 750 | 150 |
| Pune | 2024-04 | 620 | 900 | -280 |
How to solve Month-Over-Month Delivered GMV Change by City
Window functions work on rows, so first make the rows the report talks about: one per city and month. Group the delivered orders by city and DATE_FORMAT(placed_at, '%Y-%m') (or LEFT(placed_at, 7) — the first seven characters of a fixed-format datetime are the month) and sum the values. Cancelled orders are filtered out before grouping, so a month with only cancellations produces no row at all.
Then LAG(gmv) OVER (PARTITION BY city ORDER BY month) puts the city's previous monthly row beside each row. Partitioning restarts the sequence for each city, so a city's first month gets NULL; and since a month with no deliveries has no row, the previous row is the previous month with deliveries — April's comparison in the example reaches back to February. The change is a plain subtraction, NULL when prev_gmv is NULL.
The correlated alternative looks up, for each month, the latest earlier month of the same city with ORDER BY month DESC LIMIT 1; it is quadratic in the number of months, where the window is one sort.
Reference solution (MySQL)
WITH monthly AS (
SELECT city, DATE_FORMAT(placed_at, '%Y-%m') AS month, SUM(order_value) AS gmv
FROM Orders
WHERE status = 'delivered'
GROUP BY city, DATE_FORMAT(placed_at, '%Y-%m')
)
SELECT city, month, gmv,
LAG(gmv) OVER (PARTITION BY city ORDER BY month) AS prev_gmv,
gmv - LAG(gmv) OVER (PARTITION BY city ORDER BY month) AS gmv_change
FROM monthly
ORDER BY city, monthAnother way
WITH monthly AS (
SELECT city, LEFT(placed_at, 7) AS month, SUM(order_value) AS gmv
FROM Orders WHERE status = 'delivered' GROUP BY city, LEFT(placed_at, 7)
), paired AS (
SELECT m.city, m.month, m.gmv,
(SELECT p.gmv FROM monthly p WHERE p.city = m.city AND p.month < m.month ORDER BY p.month DESC LIMIT 1) AS prev_gmv
FROM monthly m
)
SELECT city, month, gmv, prev_gmv, gmv - prev_gmv AS gmv_change FROM paired ORDER BY city, month← Rider Login Hours and July Incentive Tier · Packing Slip Item List for Each Quick Order →