Sweaty Imposter

Median Salary per Department

SQLAggregationSeniorStaffUpdated
Return the median salary per department in PostgreSQL. Use a continuous median (interpolate between the two middle values for even-sized groups).

Schema

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  department TEXT NOT NULL,
  salary     NUMERIC NOT NULL
);

Drill This: Your Query First

The Correct Query

Option (b)

SELECT department,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) is Postgres's continuous median and interpolates between the two middle values. PERCENTILE_DISC (c) returns the lower of the two middle values, which is a discrete median. AVG (a) is the mean, not the median. (d) uses a MEDIAN() function that does not exist in Postgres.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT department, AVG(salary) AS median_salary
FROM employees
GROUP BY department;

Option (c)

SELECT department,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

Option (d)

SELECT DISTINCT department,
       MEDIAN(salary) OVER (PARTITION BY department) AS median_salary
FROM employees;