Median Card Spend by Merchant Category

Hard
69.

Median Card Spend by Merchant Category

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

ColumnTypeValues
txn_idint
categoryenumDINING, ELECTRONICS, FUEL, GROCERY, TRAVEL
amountint
txn_statusenumSETTLED, DECLINED

Primary key: txn_id.

One row per card authorisation; amount is in rupees.

Example

The rows must come back in the order shown.

CardSpend

txn_idcategoryamounttxn_status
1GROCERY850SETTLED
2GROCERY2400SETTLED
3GROCERY610SETTLED
4FUEL1500SETTLED
5FUEL2000SETTLED
6FUEL50000DECLINED
7TRAVEL18500SETTLED
8TRAVEL4200SETTLED
9TRAVEL4200SETTLED
10TRAVEL96000SETTLED
1 more row

Output

categorysettled_txnsmedian_amount
FUEL21750
GROCERY3850
TRAVEL411350
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.

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

ColumnType
txn_idint
categoryenum(DINING, ELECTRONICS, FUEL, GROCERY, TRAVEL)
amountint
txn_statusenum(SETTLED, DECLINED)

Primary key: txn_id.

One row per card authorisation; amount is in rupees.

Examples

Example 1

CardSpend

txn_idcategoryamounttxn_status
1GROCERY850SETTLED
2GROCERY2400SETTLED
3GROCERY610SETTLED
4FUEL1500SETTLED
5FUEL2000SETTLED
6FUEL50000DECLINED
7TRAVEL18500SETTLED
8TRAVEL4200SETTLED
9TRAVEL4200SETTLED
10TRAVEL96000SETTLED
11DINING1250DECLINED

Output

categorysettled_txnsmedian_amount
FUEL21750
GROCERY3850
TRAVEL411350

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 category

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

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