Median in SQL without a MEDIAN function
The question: Find the median salary. With an even number of people, it's the average of the two middle salaries.
employees
| emp_id | salary |
|---|---|
| 1 | 40000 |
| 2 | 50000 |
| 3 | 60000 |
| 4 | 70000 |
| 5 | 80000 |
| 6 | 900000 |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT AVG(salary) AS median_salary FROM employees;
AVG is the mean, not the median. One salary of 900,000 pulls it up to 200,000, more than five of the six people earn.
The answer
SELECT AVG(salary) AS median_salary
FROM (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS n
FROM employees
)
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);Number the salaries from lowest to highest and keep the middle one (odd count) or the middle two (even count), then average what's left: (60,000 + 70,000) / 2 = 65,000.
(n + 1) / 2 and (n + 2) / 2 use whole-number division: for 6 rows they're 3 and 4, for 5 rows both are 3. PostgreSQL also has PERCENTILE_CONT(0.5).
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.