Top 3 Highest-Paid Employees in Each Department

SQL coding challenge · Difficulty: medium · +100 XP

Problem

The annual bonus pool goes to the top 3 earners in each department. Find those employees.

Tables

Table: departments

| department_id | department_name |
| 1 | Engineering |
| 2 | Marketing |
| 3 | HR |
| 4 | Finance |

Table: employees

| employee_id | first_name | department_id | salary | hire_date | manager_id |
| 1 | Alice | 1 | 90000 | 2021-03-10 | NULL |
| 2 | Bob | 1 | 75000 | 2021-03-20 | 1 |
| 3 | Charlie | 1 | 80000 | 2021-03-25 | 1 |
| 4 | Victor | 1 | 65000 | 2022-01-15 | 1 |
| 5 | Diana | 2 | 70000 | 2022-06-05 | 1 |
| 6 | Eve | 2 | 65000 | 2021-11-15 | 5 |
| 7 | Frank | 3 | 60000 | 2023-02-28 | 1 |

Expected Output

| department_id | employee_id | first_name | salary |
| 1 | 1 | Alice | 90000 |
| 1 | 3 | Charlie | 80000 |
| 1 | 2 | Bob | 75000 |
| 2 | 5 | Diana | 70000 |
| 2 | 6 | Eve | 65000 |
| 3 | 7 | Frank | 60000 |

Return: department_id, employee_id, first_name, salary
Top 3 per department (include ties at rank 3)
Sort by department_id, then salary descending
Hint: Use RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and filter where rank <= 3