RANK and DENSE_RANK without window functions

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Subqueries. About 16 minutes. Part of the Pro drill bank.

staff_salaries contains compensation records with columns staff_id, name, and salary. Compute both standard rank (rank_val) and dense rank (dense_rank_val) ordered by salary descending, using scalar subqueries without the OVER clause. rank_val: rank with gaps after ties (e.g. 1, 2, 2, 4). dense_rank_val: rank without gaps after ties (e.g. 1, 2, 2, 3). Return staff_id, name, salary, rank_val, and dense_rank_val. Order by salary descending, staff_id ascending.

Requirements

• Output columns: staff_id, name, salary, rank_val, dense_rank_val.
• Order by salary descending, staff_id ascending.

Constraints

• salary is an integer.
• staff_id is unique.
• Do not use the OVER clause.

Examples

Input: staff_salaries staff_id,name,salary 1,Aarav,90000 2,Diya,85000 3,Kabir,85000 4,Meera,70000 5,Rohan,60000 Output: staff_id | name | salary | rank_val | dense_rank_val 1 | Aarav | 90000 | 1 | 1 2 | Diya | 85000 | 2 | 2 3 | Kabir | 85000 | 2 | 2 4 | Meera | 70000 | 4 | 3 5 | Rohan | 60000 | 5 | 4 Why this passes: Diya and Kabir tie at 85000, so RANK gives both 2 and then skips to 4, while DENSE_RANK gives both 2 and continues with 3.

Input: staff_salaries staff_id | name | salary 1 | X | 50 2 | Y | 40 Output: staff_id | name | salary | rank_val | dense_rank_val 1 | X | 50 | 1 | 1 2 | Y | 40 | 2 | 2 Why this passes: No ties, both rankings advance 1, 2.

Topics: lakebench, sql, correlated-subquery, ranking.

More SQL interview questions · All interview problems · Learn data engineering