The median salary

Find the median salary of all employees, and then the median salary in each department.

What it tests

Whether you can build a statistic that SQL doesn't always provide, using row numbers and counts — and handle both odd and even group sizes.

The pattern

Median with ROW_NUMBER and COUNT. "Median", "middle value", "50th percentile".

How to approach it

  1. Sort the salaries and number them 1 to n with ROW_NUMBER; get n with COUNT(*) OVER ().
  2. The middle positions are (n + 1) / 2 and (n + 2) / 2 using whole-number division: the same row when n is odd, the two middle rows when n is even.
  3. Average the salaries at those positions.
  4. For each department, add PARTITION BY dept_id to both window functions and GROUP BY dept_id.

Edge cases to mention

Common mistakes

Not true: AVG(salary) returns the median salary.
In fact: AVG returns the mean: the total divided by the count. The median is the middle value once the rows are sorted, and one huge salary moves the mean far more than the median.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.