SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…