Return the order status, the total order count, the interpolated median `total_amount`, and the interpolated 90th-percentile `total_amount` for each `status`. Sort the final result by `status` ascending

The shape

A grouped query that returns three numbers per status: how many orders the status has, the interpolated median of those orders' amounts, and the interpolated 90th percentile. COUNT(*) and the two PERCENTILE_CONT calls are all aggregates running over the same group, so they collapse the orders inside each status into a single summary row. The 0.5 and 0.9 positions sit inside the same sorted set per status; the function just walks farther into the sort for the 90th percentile.

Clause by clause

• SELECT status, COUNT(*) AS order_count, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_amount, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_amount) AS p90_amount returns one row per status with three measurements. COUNT(*) counts every order in the status; each PERCENTILE_CONT sorts the status's total_amount values ascending and picks the value at its position, interpolating between adjacent rows when the position falls between them.
• FROM orders reads every order.
• GROUP BY status partitions the orders by status, which is what makes the three aggregates run per status instead of across the entire table.
• ORDER BY status is the outer sort. It prints the statuses alphabetically so the per-status summary reads in a stable order.

Why PERCENTILE_CONT for both

The prompt asks for interpolated values at both positions. The interpolation matters most at p90, where a small-count status may have only a handful of orders and the 90th-percentile position lands well between two actual order amounts. PERCENTILE_CONT produces a smooth value at that position; PERCENTILE_DISC would jump to whichever actual order happens to sit just above the 90th-percentile row. For a distribution summary, the smoother value reads more cleanly as a threshold.

The trap

The percentile functions run per group because of GROUP BY status, but the WITHIN GROUP (ORDER BY total_amount) clause inside each function is a separate, function-local sort. Reading the query, the two GROUP BY-flavored words look related, but they are not the same machinery: GROUP BY status partitions rows for the outer aggregation; WITHIN GROUP (ORDER BY total_amount) sorts the per-partition values that each ordered-set aggregate then walks. Confusing the two leads to writing a redundant ORDER BY total_amount at the query level, which does nothing useful here because the printed output is one row per status. The inner sort is the one that matters for the percentile calculation.