Monthly GMV Against the Previous Calendar Month

Medium
112.

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. 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

ColumnTypeValues
order_idint
placed_atdatetime
order_totalint
statusenumplaced, packed, shipped, delivered, cancelled

Primary key: order_id.

One row per order; order_total in rupees.

Example

The rows must come back in the order shown.

orders

order_idplaced_atorder_totalstatus
12024-01-05 10:00:001200delivered
22024-01-20 19:30:00800delivered
32024-02-02 08:15:002500shipped
42024-02-14 21:00:00999cancelled
52024-04-01 12:00:003100delivered
62024-05-11 16:45:001700placed
72024-05-30 23:59:00600delivered

Output

monthgmvprev_month_gmvchange
2024-012000NULLNULL
2024-0225002000500
2024-043100NULLNULL
2024-0523003100-800
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.

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

ColumnType
order_idint
placed_atdatetime
order_totalint
statusenum(placed, packed, shipped, delivered, cancelled)

Primary key: order_id.

One row per order; order_total in rupees.

Examples

Example 1

orders

order_idplaced_atorder_totalstatus
12024-01-05 10:00:001200delivered
22024-01-20 19:30:00800delivered
32024-02-02 08:15:002500shipped
42024-02-14 21:00:00999cancelled
52024-04-01 12:00:003100delivered
62024-05-11 16:45:001700placed
72024-05-30 23:59:00600delivered

Output

monthgmvprev_month_gmvchange
2024-012000NULLNULL
2024-0225002000500
2024-043100NULLNULL
2024-0523003100-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 month

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