Brightlane's operations team is comparing two median-calculation methods within each order-status group.
Write a query to return the order status, the interpolated median total_amount, and the actual-value median total_amount for each status. Sort the final result by status ascending.
Assumptions:
- Each unique
statusvalue should appear once. - The interpolated median sorts the values within a status ascending and picks the value at the midpoint position, interpolating linearly between the two middle values when the count is even. The result may not equal any actual order amount.
- The actual-value median sorts the values within a status ascending and picks the actual value at or above the midpoint position. The result is always one of the actual order amounts (the lower of the two middle values when the count is even).
- The final result is sorted by
statusascending.
Output:
- One row per status, with columns
status,median_cont, andmedian_disc. Sorted bystatus.
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
status,
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY
total_amount
) AS median_cont,
PERCENTILE_DISC(0.5) WITHIN GROUP (
ORDER BY
total_amount
) AS median_disc
FROM
orders
GROUP BY
status
ORDER BY
statusThe shape
Two percentile functions on the same column at the same percentile, grouped by status, expose the difference between an interpolated median and an actual-value median side by side. PERCENTILE_CONT(0.5) and PERCENTILE_DISC(0.5) both ask for the 50th percentile of total_amount within each status, but they answer differently when the row count is even: CONT interpolates between the two middle values, DISC returns the lower of them verbatim.
Clause by clause
SELECT status, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_cont, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_discreturns the status and both medians in a single row per status. Each percentile function carries its ownWITHIN GROUP (ORDER BY total_amount)because each is a separate ordered-set aggregate; they happen to use the same sort, but the syntax requires the clause inside each function call.FROM ordersreads every order. NoWHERE.GROUP BY statuspartitions the orders by status, which is what reduces the result to one row per status.ORDER BY statusis the outer sort. It prints the statuses alphabetically so the comparison reads in a stable order.
Why both functions on the same row
The whole point of the analysis is to compare the two methods. Computing them on the same status, in the same query, on the same row of output, makes the comparison direct: the operations team reads median_cont next to median_disc and sees exactly where they agree and where they diverge. When a status has an odd row count, the two columns will be equal; when it has an even row count, they will typically differ.
The trap
WITHIN GROUP (ORDER BY ...) is not the same syntax as OVER (ORDER BY ...). The first defines the sort for an ordered-set aggregate; the second defines the sort for a window function. These two percentile functions are aggregates here, not window functions, so they live inside GROUP BY status and collapse rows. Writing OVER (...) instead of WITHIN GROUP (...) here would turn them into window functions and the query would no longer return one row per status.
You practiced PERCENTILE_CONT vs PERCENTILE_DISC side by side — both within WITHIN GROUP (ORDER BY ...); _CONT interpolates between values, _DISC returns an actual value from the input.
