Median Card Spend by Merchant Category — SQL Problem
Averages are skewed by a few huge purchases, so the cards team reports the median settled transaction amount for each merchant category.
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #69
Problem statement
Averages are skewed by a few huge purchases, so the cards team reports the median settled transaction amount for each merchant category. With an odd number of settled transactions the median is the middle amount; with an even number it is the mean of the two middle amounts. Declined transactions are ignored, and a category with no settled transaction does not appear.
Return category, settled_txns and median_amount (rounded to 2 decimals), ordered by category.
Tables
Table: CardSpend
| Column | Type |
|---|---|
| txn_id | int |
| category | enum(DINING, ELECTRONICS, FUEL, GROCERY, TRAVEL) |
| amount | int |
| txn_status | enum(SETTLED, DECLINED) |
Primary key: txn_id.
One row per card authorisation; amount is in rupees.
Examples
Example 1
CardSpend
| txn_id | category | amount | txn_status |
|---|---|---|---|
| 1 | GROCERY | 850 | SETTLED |
| 2 | GROCERY | 2400 | SETTLED |
| 3 | GROCERY | 610 | SETTLED |
| 4 | FUEL | 1500 | SETTLED |
| 5 | FUEL | 2000 | SETTLED |
| 6 | FUEL | 50000 | DECLINED |
| 7 | TRAVEL | 18500 | SETTLED |
| 8 | TRAVEL | 4200 | SETTLED |
| 9 | TRAVEL | 4200 | SETTLED |
| 10 | TRAVEL | 96000 | SETTLED |
| 11 | DINING | 1250 | DECLINED |
Output
| category | settled_txns | median_amount |
|---|---|---|
| FUEL | 2 | 1750 |
| GROCERY | 3 | 850 |
| TRAVEL | 4 | 11350 |
How to solve Median Card Spend by Merchant Category
MySQL has no MEDIAN aggregate, so the median is built from positions. Inside each category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount) numbers the settled amounts 1…c and COUNT(*) OVER (PARTITION BY category) attaches c to each row. The middle positions are FLOOR((c + 1) / 2) and FLOOR((c + 2) / 2): for c = 3 both are 2, for c = 4 they are 2 and 3. Keeping those rows and averaging them gives the median for odd and even counts alike; filtering declined rows before numbering is what keeps them out.
Ties do not break anything: two transactions of ₹4,200 are two rows with two positions, so the median of 4,200, 4,200, 18,500, 96,000 is the mean of positions 2 and 3 — (4,200 + 18,500) / 2. The ascending/descending variant uses the same fact: a row is in the middle when its rank from the bottom and from the top differ by at most one.
The window-free alternative uses the definition: a value is a middle value when at least half of the category's amounts are ≤ it and at least half are ≥ it. For an even count the two middle values pass; for equal middle values only one distinct value passes — hence AVG(DISTINCT …). That form is quadratic per category; the window form is one sort per category.
Reference solution (MySQL)
WITH ranked AS (
SELECT category, amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount) AS rn,
COUNT(*) OVER (PARTITION BY category) AS cnt
FROM CardSpend
WHERE txn_status = 'SETTLED'
)
SELECT category, MAX(cnt) AS settled_txns, ROUND(AVG(amount), 2) AS median_amount
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY category
ORDER BY categoryAnother way
WITH s AS (SELECT txn_id, category, amount FROM CardSpend WHERE txn_status = 'SETTLED')
SELECT t.category, (SELECT COUNT(*) FROM s c WHERE c.category = t.category) AS settled_txns, ROUND(AVG(DISTINCT t.amount), 2) AS median_amount
FROM s t
WHERE (SELECT COUNT(*) FROM s u WHERE u.category = t.category AND u.amount <= t.amount) * 2 >= (SELECT COUNT(*) FROM s c WHERE c.category = t.category)
AND (SELECT COUNT(*) FROM s u WHERE u.category = t.category AND u.amount >= t.amount) * 2 >= (SELECT COUNT(*) FROM s c WHERE c.category = t.category)
GROUP BY t.category ORDER BY t.categoryAnother way
WITH ranked AS (
SELECT category, amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount, txn_id) AS up,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC, txn_id DESC) AS down
FROM CardSpend WHERE txn_status = 'SETTLED'
)
SELECT category, COUNT(*) AS settled_txns, ROUND(AVG(CASE WHEN up + 1 >= down AND down + 1 >= up THEN amount END), 2) AS median_amount
FROM ranked GROUP BY category ORDER BY category← Savings Accounts Below the Minimum Average Balance · UPI App New-User Retention in the Following Month →