Practice — Case Study: The SQL Interview Gauntlet (5 questions)

You're given this table:

salaries
employee_id | department_id | salary
------------+----------------+-------
1           | 10             | 60000
2           | 10             | 72000
3           | 10             | 85000
4           | 10             | 90000
5           | 20             | 55000
6           | 20             | 61000
7           | 20             | 70000

Compute the median salary per department. Department 10 has an even number of rows (4), department 20 has an odd number (3) — your query needs to handle both correctly, since the median definition differs (average the two middle values vs. take the single middle value).

1. Write a solution using PERCENTILE_CONT, and explain what the 0.5 and WITHIN GROUP (ORDER BY ...) actually mean mechanically.
2. Now write a solution that computes the same result without any built-in median/percentile function, using only ROW_NUMBER(), COUNT(), and standard aggregation — the way you'd need to on an engine that doesn't support PERCENTILE_CONT.
3. Which would you actually use in production, and why?