Compute median salary per department
Return the median non-NULL salary of each non-NULL department, averaging the two middle values for even-sized groups and rounding to two decimals. Omit departments without a non-NULL salary. Return only the requested columns and follow the specified ordering. This question probes select the middle one or two row positions using integer inequalities, then average them. this avoids mysql versus postgresql differences in percentile aggregate support.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
department_id, median_salary; ascending department_id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'A',10,NULL,100),(2,'B',10,NULL,200),(3,'C',10,NULL,400),(4,'D',10,NULL,500),(5,'E',20,NULL,100),(6,'F',20,NULL,200),(7,'G',20,NULL,900);
Expected result
department_id | median_salary 10 | 300.0 20 | 200.0
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
department_id | median_salary 10 | 100.0
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Number sorted salaries and count each partition.
- Select positions satisfying cnt <= 2*rn <= cnt+2, then average.
Target complexity: time O(n log n) · space O(n)