DENSE_RANK: Rank Employees Without Gaps

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The payroll system needs salary rankings where tied employees share a rank but the next rank is never skipped *(class standings: 1st, 1st, 2nd — not 1st, 1st, 3rd)*.

Tables

Table: employees_wf

| emp_id | emp_name | department | salary |
| 101 | John | IT | 90000 |
| 102 | Alice | IT | 75000 |
| 103 | Bob | IT | 75000 |
| 104 | David | HR | 80000 |
| 105 | Emma | HR | 65000 |

Expected Output

| emp_id | emp_name | salary | dense_rank |
| 101 | John | 90000 | 1 |
| 104 | David | 80000 | 2 |
| 102 | Alice | 75000 | 3 |
| 103 | Bob | 75000 | 3 |
| 105 | Emma | 65000 | 4 |

• Return: emp_id, emp_name, salary, dense_rank
• Tied employees share a rank, but next rank is never skipped
• Sort by salary descending
• Function to use: DENSE_RANK()