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 OverviewInput: Employee + DepartmentnamesalarydeptMax90000ITJoe85000ITHenry80000SalesSam60000SalesDENSE_RANKTOP 3Output: Top 3 Per DepartmentDepartmentEmployeeSalaryITMax90000ITJoe85000SalesHenry80000SalesSam60000Window Function: PARTITION BY department ORDER BY salary DESCEach department ranked separately, top 3 unique salary levels included
Understanding the Visualization
1
Join Tables
Combine Employee and Department data
2
Rank Salaries
Use DENSE_RANK within each department
3
Filter Results
Keep only top 3 ranks per department
Key Takeaway
🎯 Key Insight: Use DENSE_RANK() when you need top N unique values per group, handling ties gracefully

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