Compare RANK and DENSE_RANK for salaries
For every employee with a non-NULL salary, return both RANK and DENSE_RANK by descending salary across the company. Tied salaries share ranks. Return only the requested columns and follow the specified ordering. This question probes rank leaves gaps after ties while dense_rank counts distinct levels; put id only in final ordering to avoid breaking salary ties.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
id, salary, salary_rank, dense_salary_rank; descending salary then ascending 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,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
id | salary | salary_rank | dense_salary_rank 4 | 300 | 1 | 1 2 | 200 | 2 | 2 3 | 200 | 2 | 2 1 | 100 | 4 | 3
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
id | salary | salary_rank | dense_salary_rank 1 | 100 | 1 | 1 2 | 100 | 1 | 1
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Use identical salary ordering in both windows.
- Do not add ID inside either ranking window.
Target complexity: time O(n log n) · space O(n)