Month-Over-Month Delivered GMV Change by City

Medium
161.

Month-Over-Month Delivered GMV Change by City

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

ColumnTypeValues
order_idint
cityvarchar
placed_atdatetime
order_valueint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order, value in rupees; city is the delivery city.

Example

The rows must come back in the order shown.

Orders

order_idcityplaced_atorder_valuestatus
1Pune2024-01-05 13:00:00400delivered
2Pune2024-01-20 20:00:00350delivered
3Pune2024-02-11 19:30:00900delivered
4Pune2024-02-12 21:00:00500cancelled
5Pune2024-04-02 12:40:00620delivered
6Kochi2024-01-31 23:50:00280delivered
7Kochi2024-02-01 00:10:00260delivered
8Kochi2024-03-15 13:15:00300cancelled

Output

citymonthgmvprev_gmvgmv_change
Kochi2024-01280NULLNULL
Kochi2024-02260280-20
Pune2024-01750NULLNULL
Pune2024-02900750150
Pune2024-04620900-280
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
cityvarchar
placed_atdatetime
order_valueint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order, value in rupees; city is the delivery city.

Examples

Example 1

Orders

order_idcityplaced_atorder_valuestatus
1Pune2024-01-05 13:00:00400delivered
2Pune2024-01-20 20:00:00350delivered
3Pune2024-02-11 19:30:00900delivered
4Pune2024-02-12 21:00:00500cancelled
5Pune2024-04-02 12:40:00620delivered
6Kochi2024-01-31 23:50:00280delivered
7Kochi2024-02-01 00:10:00260delivered
8Kochi2024-03-15 13:15:00300cancelled

Output

citymonthgmvprev_gmvgmv_change
Kochi2024-01280NULLNULL
Kochi2024-02260280-20
Pune2024-01750NULLNULL
Pune2024-02900750150
Pune2024-04620900-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, month

Another 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 →