Apple: Apple Pay Monthly Volume Change by Country
The Apple Pay business team reviews payment volume month by month in each market.
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #610
Problem statement
The Apple Pay business team reviews payment volume month by month in each market.
For each country and each month in which it had transactions, return country, month (as 'YYYY-MM'), volume (the sum of amount_usd that month) and change_vs_prev: this month's volume minus the volume of the previous calendar month. If the country had no transactions in the previous calendar month, change_vs_prev is NULL — even when it had volume in some earlier month. Order the rows by country, then month.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: PayTransaction
| Column | Type |
|---|---|
| txn_id | int |
| country | varchar |
| txn_date | date |
| amount_usd | int |
Primary key: txn_id.
One row per Apple Pay transaction.
Examples
Example 1
PayTransaction
| txn_id | country | txn_date | amount_usd |
|---|---|---|---|
| 1 | India | 2024-11-03 | 120 |
| 2 | India | 2024-11-20 | 80 |
| 3 | India | 2024-12-05 | 260 |
| 4 | India | 2025-02-14 | 90 |
| 5 | Japan | 2024-12-31 | 300 |
| 6 | Japan | 2025-01-01 | 150 |
| 7 | Japan | 2025-01-09 | 50 |
Output
| country | month | volume | change_vs_prev |
|---|---|---|---|
| India | 2024-11 | 200 | NULL |
| India | 2024-12 | 260 | 60 |
| India | 2025-02 | 90 | NULL |
| Japan | 2024-12 | 300 | NULL |
| Japan | 2025-01 | 200 | -100 |
How to solve Apple: Apple Pay Monthly Volume Change by Country
First collapse the transactions into one row per country and month: DATE_FORMAT(txn_date, '%Y-%m') gives the label, and a month number YEAR * 12 + MONTH gives something you can do arithmetic on (December 2024 and January 2025 are consecutive numbers, which the labels are not).
The trap is that LAG(volume) returns the previous row of the country, and a country with no transactions in January has December as its previous row before February. The statement wants the previous calendar month, so the query also lags the month number and only subtracts when prev_no = month_no − 1; otherwise the CASE has no ELSE and yields NULL. The first month of each country is NULL for the same reason.
The alternative avoids the window entirely: a LEFT JOIN of the monthly table to itself on month_no − 1. A missing previous month simply fails to match and the subtraction gives NULL. Both group the transactions once; the join version reads the monthly rows twice.
Reference solution (MySQL)
WITH monthly AS (
SELECT country, DATE_FORMAT(txn_date, '%Y-%m') AS month,
YEAR(txn_date) * 12 + MONTH(txn_date) AS month_no,
SUM(amount_usd) AS volume
FROM PayTransaction
GROUP BY country, DATE_FORMAT(txn_date, '%Y-%m'), YEAR(txn_date) * 12 + MONTH(txn_date)
), with_prev AS (
SELECT country, month, month_no, volume,
LAG(month_no) OVER (PARTITION BY country ORDER BY month_no) AS prev_no,
LAG(volume) OVER (PARTITION BY country ORDER BY month_no) AS prev_volume
FROM monthly
)
SELECT country, month, volume,
CASE WHEN prev_no = month_no - 1 THEN volume - prev_volume END AS change_vs_prev
FROM with_prev
ORDER BY country, monthAnother way
WITH monthly AS (
SELECT country, month, MIN(month_no) AS month_no, SUM(amount_usd) AS volume
FROM (SELECT country, LEFT(txn_date, 7) AS month, YEAR(txn_date) * 12 + MONTH(txn_date) AS month_no, amount_usd FROM PayTransaction) t
GROUP BY country, month
)
SELECT c.country, c.month, c.volume, c.volume - p.volume AS change_vs_prev
FROM monthly c
LEFT JOIN monthly p ON p.country = c.country AND p.month_no = c.month_no - 1
ORDER BY c.country, c.month← Apple: Genius Bar Repeat Visits Within 14 Days · Apple: Component Batches Below Their Pooled Test Yield →