Top three salary levels per department
Return employees at the top three distinct non-NULL salary levels within each non-NULL department, including all ties. Return only the requested columns and follow the specified ordering. This question probes partitioned dense_rank retains ties at the third distinct salary boundary.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
department_id, id, salary, salary_rank; ascending department_id, salary_rank, 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,400),(2,'B',10,NULL,300),(3,'C',10,NULL,300),(4,'D',10,NULL,200),(5,'E',10,NULL,100),(6,'F',20,NULL,50);
Expected result
department_id | id | salary | salary_rank 10 | 1 | 400 | 1 10 | 2 | 300 | 2 10 | 3 | 300 | 2 10 | 4 | 200 | 3 20 | 6 | 50 | 1
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 | id | salary | salary_rank 10 | 1 | 100 | 1 10 | 2 | 100 | 1
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Rank each department separately.
- Filter in an outer query because window results are not available in WHERE.
Target complexity: time O(n log n) · space O(n)