Drill 8 of 9 in CTEs & Reshaping Data
0

CTEs & Reshaping Data · PERCENTILE_CONT · ordered-set aggregates

8. 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…