Department Top Three Salaries - Problem
Find Top 3 Salary Earners in Each Department
You are a data analyst at a large corporation tasked with identifying the high earners across all departments. The company defines a high earner as an employee whose salary ranks in the top 3 unique salaries within their department.
Given two database tables:
•
•
Your goal is to write a SQL query that returns all employees who are high earners in their respective departments. Note that if a department has multiple employees with the same salary, they all count as having the same rank.
Example: If a department has salaries [90000, 85000, 85000, 60000], the top 3 unique salaries are [90000, 85000, 60000], so all employees with these salaries should be included.
You are a data analyst at a large corporation tasked with identifying the high earners across all departments. The company defines a high earner as an employee whose salary ranks in the top 3 unique salaries within their department.
Given two database tables:
•
Employee - Contains employee information (id, name, salary, departmentId)•
Department - Contains department information (id, name)Your goal is to write a SQL query that returns all employees who are high earners in their respective departments. Note that if a department has multiple employees with the same salary, they all count as having the same rank.
Example: If a department has salaries [90000, 85000, 85000, 60000], the top 3 unique salaries are [90000, 85000, 60000], so all employees with these salaries should be included.
Input & Output
example_1.sql — Standard Case
$
Input:
Employee table:
| id | name | salary | departmentId |
|----|-------|--------|-------------|
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
Department table:
| id | name |
|----|-------|
| 1 | IT |
| 2 | Sales |
›
Output:
| Department | Employee | Salary |
|------------|----------|--------|
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
💡 Note:
In IT dept: Top 3 unique salaries are 90000, 85000, 70000, 69000. Since we need top 3, we include employees with salaries 90000, 85000 (Joe & Randy both), and 70000. In Sales: Only 2 unique salaries exist (80000, 60000), so both employees are included.
example_2.sql — Ties and Ranking
$
Input:
Employee table:
| id | name | salary | departmentId |
|----|------|--------|-------------|
| 1 | A | 100000 | 1 |
| 2 | B | 90000 | 1 |
| 3 | C | 90000 | 1 |
| 4 | D | 90000 | 1 |
| 5 | E | 80000 | 1 |
| 6 | F | 70000 | 1 |
Department table:
| id | name |
|----|------|
| 1 | Tech |
›
Output:
| Department | Employee | Salary |
|------------|----------|--------|
| Tech | A | 100000 |
| Tech | B | 90000 |
| Tech | C | 90000 |
| Tech | D | 90000 |
| Tech | E | 80000 |
💡 Note:
Top 3 unique salaries are: 100000 (rank 1), 90000 (rank 2), 80000 (rank 3). All employees with these salaries are included. Employee F with 70000 has rank 4, so excluded.
example_3.sql — Small Department
$
Input:
Employee table:
| id | name | salary | departmentId |
|----|------|--------|-------------|
| 1 | John | 50000 | 1 |
| 2 | Jane | 60000 | 1 |
Department table:
| id | name |
|----|------|
| 1 | HR |
›
Output:
| Department | Employee | Salary |
|------------|----------|--------|
| HR | Jane | 60000 |
| HR | John | 50000 |
💡 Note:
This department has only 2 employees with 2 unique salaries. Since both salaries are in the 'top 3', both employees are included in the result.
Visualization
Tap to expand
Understanding the Visualization
1
Group by Department
Separate employees into their respective departments
2
Rank Salaries
Within each department, rank salaries from highest to lowest
3
Handle Ties
Employees with same salary get same rank (like shared medals)
4
Select Top 3
Keep only employees with ranks 1, 2, or 3 (Gold, Silver, Bronze levels)
Key Takeaway
🎯 Key Insight: DENSE_RANK() window function with PARTITION BY naturally solves the "top N per group" pattern while handling ties elegantly - it's like having an automated award system that never makes mistakes!
Time & Space Complexity
Time Complexity
O(n log n)
Single scan plus sorting for ranking within each department
⚡ Linearithmic
Space Complexity
O(n)
Space for intermediate ranking results
⚡ Linearithmic Space
Constraints
- 1 ≤ Employee.id ≤ 105
- 1 ≤ Department.id ≤ 100
- Employee.name and Department.name are varchar(255)
- 1 ≤ salary ≤ 106
- departmentId is a valid foreign key to Department.id
- Each department has at least 1 employee
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code