Department Top Three Salaries - Problem

You have two tables: Employee and Department.

The Employee table contains employee information including their salary and department ID. The Department table contains department names.

A company's executives want to see who earns the most money in each department. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Task: Find all employees who are high earners in their respective departments.

Note: The result should include the department name, employee name, and salary. Return results in any order.

Table Schema

Employee
Column Name Type Description
id PK int Primary key, unique employee identifier
name varchar Employee name
salary int Employee salary
departmentId int Foreign key referencing Department.id
Primary Key: id
Department
Column Name Type Description
id PK int Primary key, unique department identifier
name varchar Department name
Primary Key: id

Input & Output

Example 1 — Multiple Departments with Ties
Input Tables:
Employee
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
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 the IT department: Max has the highest salary (90000, rank 1), Joe and Randy tie for second highest (85000, rank 2), and Will has the third highest (70000, rank 3). In Sales department: Henry has the highest (80000, rank 1) and Sam has second highest (60000, rank 2). All are within top 3 unique salaries for their departments.
Example 2 — Department with Few Employees
Input Tables:
Employee
id name salary departmentId
1 Alice 95000 1
2 Bob 75000 1
Department
id name
1 Engineering
Output:
Department Employee Salary
Engineering Alice 95000
Engineering Bob 75000
💡 Note:

Engineering department has only 2 employees, both qualify as top 3 earners. Alice ranks 1st with 95000, Bob ranks 2nd with 75000. Since there are fewer than 3 unique salary levels, all employees are included.

Constraints

  • 1 ≤ Employee.id ≤ 100
  • 1 ≤ Department.id ≤ 100
  • 1 ≤ salary ≤ 1000000
  • Employee names and department names are non-empty strings
  • Each employee belongs to exactly one department

Visualization

Tap to expand
Department Top Three Salaries INPUT TABLES Employee Table: id name salary dept_id 1 Joe 85000 1 2 Henry 80000 2 3 Sam 60000 2 4 Max 90000 1 5 Janet 69000 1 6 Randy 85000 1 Department Table: id name 1 IT 2 Sales dept_id --> id (FK) JOIN ON FK ALGORITHM STEPS 1 JOIN Tables Employee JOIN Department ON e.dept_id = d.id 2 Apply DENSE_RANK() PARTITION BY department ORDER BY salary DESC 3 Rank Assignment Same salary = Same rank No gaps in ranking 4 Filter Top 3 WHERE rank <= 3 IT Dept Ranking: Max: 90000 R1 Joe: 85000 R2 Randy: 85000 R2 Janet: 69000 R3 FINAL RESULT Dept Employee Salary IT Max 90000 Joe 85000 Randy 85000 Sales Henry 80000 Sam 60000 Janet: 69000 R4 Query Complete! 5 employees in top 3 salaries SELECT d.name, e.name, e.salary FROM ranked_salaries WHERE rank <= 3 Key Insight: DENSE_RANK() vs RANK(): DENSE_RANK assigns consecutive ranks without gaps. If two employees have the same salary (rank 2), the next salary gets rank 3 (not 4). This ensures we capture exactly 3 unique salary levels per department, even if multiple employees share the same salary. TutorialsPoint - Department Top Three Salaries | Optimal Solution with DENSE_RANK()

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18 Meta 15
68.4K Views
High Frequency
~18 min Avg. Time
1.8K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen