Median Salary by Department - Hard SQL Window Functions Interview Problem

Problem

HR wants the median salary for each department to complement the average. Using the employees table, return department and median_salary using PERCENTILE_CONT(0.5), ordered by department.

Schema

employees

column	type
id	INTEGER
name	TEXT
department	TEXT
salary	INTEGER

Sample Data

id	name	department	salary
1	Alice	Engineering	80000
2	Bob	Engineering	100000
3	Carol	Engineering	120000
4	Dave	HR	60000
5	Eve	HR	70000

Expected Output

department	median_salary
Engineering	100000
HR	65000

Engineering median: middle value of [80000, 100000, 120000] = 100000. HR median: average of [60000, 70000] = 65000 (interpolated).

Hints