Solving tips
- SQL has no MEDIAN aggregate; use the ordered-set function `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)`.
- PERCENTILE_CONT interpolates between the two middle values for even counts, so a group of {120,140} yields 130, not one of the endpoints.
- The ORDER BY lives inside WITHIN GROUP, while GROUP BY on the outside defines the buckets you want a median for.
Given salaries tagged by department, compute the median salary of each department.
Schema
CREATE TABLE salaries (
department TEXT,
salary INTEGER
);
Sample data:
| department | salary |
|---|
| Engineering | 100 |
| Engineering | 120 |
| Engineering | 140 |
| Engineering | 160 |
| Sales | 50 |
| Sales | 70 |
| Sales | 90 |
Task
Return one row per department with columns department and median_salary. Use the continuous (interpolated) median: for an even number of rows it is the average of the two middle values. Order by department ascending.
Expected output
| department | median_salary |
|---|
| Engineering | 130 |
| Sales | 70 |
Approach
PostgreSQL exposes the median through the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary). The WITHIN GROUP clause supplies the sort key the percentile is computed over, and GROUP BY department runs it once per department. PERCENTILE_CONT is continuous: for an even count it linearly interpolates the two central values, which is the standard definition of median.
Query
SELECT
department,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM salaries
GROUP BY department
ORDER BY department;
Walkthrough
Engineering has four salaries sorted 100, 120, 140, 160; with an even count the 0.5 percentile falls between the 2nd and 3rd values, so it interpolates (120 + 140) / 2 = 130. Sales has three salaries 50, 70, 90; the middle value is 70, and no interpolation is needed. ORDER BY department lists Engineering before Sales.
Complexity & notes
The aggregate sorts each group’s values, O(n log n) overall. PERCENTILE_CONT returns double precision, so median_salary prints as 130/70 here but as a floating value in general; wrap in ROUND(...::numeric, 2) if you need fixed decimals. Its sibling PERCENTILE_DISC(0.5) returns an actual value from the set (no interpolation), giving 120 for the Engineering group instead of 130 — pick the one the definition requires. NULL salaries are ignored by the ordered-set aggregate, like other aggregates. Dialect note: PERCENTILE_CONT is standard SQL and works in PostgreSQL, Oracle, and SQL Server; MySQL lacks it and needs a window-function workaround (ROW_NUMBER/COUNT to find the middle rows and average them).