Compute the median order value in a dialect with no MEDIAN function
Table orders(amount). Return the median amount. Assume the SQL dialect has no MEDIAN function. Handle both an odd and an even row count correctly (for an even count the median is the average of the two central values).
-- compute the median of orders.amount without a MEDIAN functionWrite the query.
Use the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), which interpolates the middle value. Without it, number the rows by amount and average the central position(s). A plain AVG returns the mean, not the median.
- ✗Returning AVG (the mean) instead of the median
- ✗Taking one central row and ignoring the even-count case
- ✗Assuming mean equals median once NULLs are removed
- →How does PERCENTILE_DISC differ from PERCENTILE_CONT here?
- →How would you compute a median per group?
The cleanest form is the SQL-standard ordered-set aggregate, supported by Postgres:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders;If the dialect truly lacks it, rank the rows and average the central position(s). Averaging the two middle rows works for an even count and still returns the single middle row for an odd count:
WITH ranked AS (
SELECT amount,
ROW_NUMBER() OVER (ORDER BY amount) AS rn,
COUNT(*) OVER () AS n
FROM orders
)
SELECT AVG(amount) AS median
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);AVG(amount) over the whole table is the mean, not the median, and a single OFFSET central row silently mishandles even counts.
