Problem Description
A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the **top three unique salaries** for that department. Write a SQL query to find the employees who are high earners in each of the departments. Return the result table ordered in any order. ### Example **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 | **Expected Output:** | Department | Employee | Salary | | :--- | :--- | :--- | | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Henry | 80000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 |
Database Schema
Sample SQL Solution
-- Use DENSE_RANK() partitioned by departmentId
WITH RankedSalaries AS (
SELECT
departmentId,
name,
salary,
DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rnk
FROM Employee
)
SELECT
d.name AS Department,
r.name AS Employee,
r.salary AS Salary
FROM RankedSalaries r
JOIN Department d ON r.departmentId = d.id
WHERE r.rnk <= 3;