Median Transaction by Category

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Problem

Compute the median transaction amount for each product category. Show each category alongside its median, walking the categories in alphabetical order.

The middle transaction in each category.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this problem really is

This is an exact per-group median dressed up as a dashboard metric. There is no one-call median here, so you have to find the middle of each category by hand: number every categorized sale from cheapest to dearest inside its category, count the category, and keep the middle position. Most candidates get that far. The even-sized category is what separates them. There is no single middle row there, and a candidate who picks one of the two central sales reports a median off by half the gap between them. The second, quieter trap is the join: sales with no product_id belong to no category and must drop out before anything is counted.

Build it in three moves

Step 1: Attach the category before numbering anything

The category lives on products, so inner-join transactions to products on product_id first. The window can only partition by p.category once every sale carries its category, and the same inner join silently drops the sales whose product_id is empty.

Step 2: Number each sale and size its category

ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY t.total_amount) gives each sale its position from the cheapest up, and COUNT(*) OVER (PARTITION BY p.category) stamps the category size on every row without collapsing them. Both windows share one partition, so each category is sorted once.

Step 3: Keep the middle one or two rows, then average

With integer division, (cnt + 1) / 2 and (cnt + 2) / 2 land on the same position when cnt is odd and on the two central positions when it is even. Filter on that pair, then take AVG(total_amount) per category: one row averages to itself, and two rows average to the true median.

WITH ranked AS (
  SELECT p.category, t.total_amount,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY t.total_amount) AS rn,
         COUNT(*) OVER (PARTITION BY p.category) AS cnt
  FROM transactions t INNER JOIN products p ON t.product_id = p.product_id
)
SELECT category, AVG(total_amount) AS median_amount
FROM ranked
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
GROUP BY category
ORDER BY category

Picks one middle row

WHERE rn = (cnt + 1) / 2 is exact on odd categories. On an even one it returns the lower central sale, so median_amount sits below the real median by half the gap to its neighbour.

Averages the middle pair

WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2) keeps one row or two, and the outer AVG turns either case into the right answer with no branching on parity.

Common follow-up questions

• Why is a median of medians not the true median by category? (Tests statistical literacy: aggregating twice loses the underlying distribution, and one product's outliers can hide inside its own median.)
• How would you weight the median by `quantity` instead of counting each sale once? (Tests whether the position logic generalizes: replace the row number with a running sum of `quantity` and keep the row where it crosses half the category total.)
• What changes if some transactions have a `NULL` `total_amount`? (Tests data hygiene: nulls sort first ascending here and still count toward `cnt`, so they must be filtered out inside the CTE before numbering.)