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
- Sort the salaries and number them 1 to n with ROW_NUMBER; get n with
COUNT(*) OVER (). - 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.
- Average the salaries at those positions.
- For each department, add PARTITION BY dept_id to both window functions and GROUP BY dept_id.
Edge cases to mention
- Even vs odd counts: the two-position trick covers both.
- NULL salaries: filter them out before numbering, or they shift the positions.
- Ties in salary don't matter: whichever tied row gets which number, the values at the middle positions are the same.
Common mistakes
- Using AVG — that is the mean, not the median.
- Taking only position n / 2, which is wrong for odd counts and only half the answer for even counts.
- Decimal division in the position maths ((n + 1) / 2.0), which gives 4.5 and matches no row.
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.