Brightlane's finance team needs the median order amount across every order as a benchmark for pricing analysis.
Write a query to return the interpolated median total_amount across every order as a single row.
Assumptions:
- The
orderstable has one row per order with atotal_amount. - The interpolated median sorts the values ascending and picks the value at the midpoint position. When the order count is even, the result is the linear interpolation between the two middle values; the result may not equal any actual order amount.
Output:
- A single row with one column,
median_order_amount, containing the interpolated median.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY
total_amount
) AS median_order_amount
FROM
ordersThe shape
PERCENTILE_CONT(0.5) collapses every order's total_amount into a single interpolated median. The WITHIN GROUP (ORDER BY total_amount) clause is what tells the function which values to sort and walk through; the 0.5 is the midpoint position. There is no GROUP BY because the aggregate runs across the whole table and returns one row.
Clause by clause
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_amountreturns the single interpolated-median value.PERCENTILE_CONTis an ordered-set aggregate, so the sort the function uses lives inside the function call viaWITHIN GROUP (ORDER BY ...), not in a separate clause. The0.5argument is the percentile position;AS median_order_amountlabels the output column so the finance team reads a domain word, not a function name.FROM ordersis the row source. Every order'stotal_amountcontributes to the calculation.
Why PERCENTILE_CONT and not PERCENTILE_DISC
The prompt asks for the interpolated median. PERCENTILE_CONT interpolates linearly between the two middle values when the order count is even, so the result may be a value that no single order actually has. PERCENTILE_DISC would instead return one of the two middle values verbatim. Both answer "the median," but only PERCENTILE_CONT produces the interpolated value the prompt specifies.
The trap
The ORDER BY lives inside WITHIN GROUP (...) parentheses, not as a separate clause on the query. Writing PERCENTILE_CONT(0.5) ORDER BY total_amount outside the function is a syntax error. The ordered-set aggregate's sort is part of its argument list, because the function is a function of an ordered set, not just a set.
You practiced PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...) — the ordered-set aggregate that returns the interpolated value at a percentile position; the result need not exist in the input data.
