Median completed order amount — SQL problem

Tables this problem reads. The full warehouse is available in thesandbox.

orders

~200 rows

Order headers. One row per order, so amount is the order total — not a line total.

order_idINTEGER— 1000–1199
customer_idTEXT— C01–C12
countryTEXT— US, DE, IN, UK, BR
amountREAL
statusTEXT— 'completed' or 'cancelled'
ordered_atTEXT— YYYY-MM-DD HH:MM:SS

Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.

SQLite cheat sheet

Truncate to dayDATE(ordered_at)
Current dateDATE('now')
ConditionalCASE WHEN … THEN … ELSE … END
String aggregateGROUP_CONCAT(col, ", ")
JSON fieldjson_extract(payload, '$.source')
Date differenceJULIANDAY(a) - JULIANDAY(b)
Filter a windowCTE, then WHERE rn = 1
MedianROW_NUMBER + COUNT(*) OVER ()

Revealed one at a time. The reference query stays in the Solution tab.

Hint 1
Number the rows by amount and get the total count in the same CTE.
Hint 2
COUNT(*) OVER () gives the row count without a second scan.
Hint 3
Rows (n+1)/2 and (n+2)/2 with integer division select the middle one or two rows.

WITH ranked AS (
  SELECT amount,
    ROW_NUMBER() OVER (ORDER BY amount) AS rn,
    COUNT(*) OVER () AS n
  FROM orders
  WHERE status = 'completed'
)
SELECT ROUND(AVG(amount), 2) AS median_amount
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

Why it works

For odd n both expressions land on the same row and AVG returns it unchanged; for even n they pick the two middle rows and AVG splits them. Warehouses expose PERCENTILE_CONT(0.5), but interviewers ask for the manual version to test frame reasoning.

20. Median completed order amount

Requirements

Why it works

Hard problems unlock with Pro