Apple: Apple Pay Monthly Volume Change by Country

Medium
610.

Apple: Apple Pay Monthly Volume Change by Country

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

ColumnType
txn_idint
countryvarchar
txn_datedate
amount_usdint

Primary key: txn_id.

One row per Apple Pay transaction.

Example

The rows must come back in the order shown.

PayTransaction

txn_idcountrytxn_dateamount_usd
1India2024-11-03120
2India2024-11-2080
3India2024-12-05260
4India2025-02-1490
5Japan2024-12-31300
6Japan2025-01-01150
7Japan2025-01-0950

Output

countrymonthvolumechange_vs_prev
India2024-11200NULL
India2024-1226060
India2025-0290NULL
Japan2024-12300NULL
Japan2025-01200-100
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.

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

ColumnType
txn_idint
countryvarchar
txn_datedate
amount_usdint

Primary key: txn_id.

One row per Apple Pay transaction.

Examples

Example 1

PayTransaction

txn_idcountrytxn_dateamount_usd
1India2024-11-03120
2India2024-11-2080
3India2024-12-05260
4India2025-02-1490
5Japan2024-12-31300
6Japan2025-01-01150
7Japan2025-01-0950

Output

countrymonthvolumechange_vs_prev
India2024-11200NULL
India2024-1226060
India2025-0290NULL
Japan2024-12300NULL
Japan2025-01200-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, month

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