Interview Drills · PERCENTILE_CONT · ordered-set aggregates
34. Median salary by department
Concept: PERCENTILE_CONT · ordered-set aggregatesshow
AVG gives the mean, which one big salary can drag up. The median is the middle value: in Postgres, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). With an even count it averages the two middle values.
Example (different data):
SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price FROM listings GROUP BY city;
Your task
HR's pay report uses medians, because one executive salary skews the averages.
Return department and median_salary for each department, sorted by department.
Starting Postgres in your browser…
Checking your access…