Department Top Three Salaries (SQL #185)

The task is to write an SQL query that will retrieve the employees who have the top three highest salaries in each department of a company, based on their unique salaries. The query should return the name of the employee, their department, and their salary. The output can be in any order.

Problem Statement

Link: LeetCode

Complexity: Hard

TableEmployee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key column for this table.
departmentId is a foreign key of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

TableDepartment

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of a department and its name.

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 an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

The query result format is in the following example.

Example 1:

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  |
+------------+----------+--------+
Explanation: 
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

Solution Explanation:
References:

Get Department Top Three Salaries

Solution to this problem is for SQL server, and uses DENSE_RANK function. If you are new to the concept, please refer to link in reference section for more details.

Note:

DENSE_RANK() is a window function in SQL that calculates the rank of a row within a result set, while also taking into account ties. This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

Get Dense Rank of Employee in each Department

SELECT 
    name,
    departmentId, 
    salary, 
    dense_rank() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rank 
FROM Employee

This query selects the name, departmentId, salary, and the dense rank of each employee’s salary within their department. We’ll use it as sub-query.

Output:
| name  | departmentId | salary | rank |
| ----- | ------------ | ------ | ---- |
| Max   | 1            | 90000  | 1    |
| Joe   | 1            | 85000  | 2    |
| Randy | 1            | 85000  | 2    |
| Will  | 1            | 70000  | 3    |
| Janet | 1            | 69000  | 4    |
| Henry | 2            | 80000  | 1    |
| Sam   | 2            | 60000  | 2    |

Get Department Name

Join sub-query with the Department table to retrieve the department names.

SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM (
  SELECT departmentId, 
         salary, 
         name,
         dense_rank() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rank 
  FROM Employee
) as e
JOIN Department as d ON e.departmentId = d.id
Output:
| Department | Employee | Salary |
| ---------- | -------- | ------ |
| IT         | Joe      | 85000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
| IT         | Max      | 90000  |
| IT         | Janet    | 69000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |

Finally, select department name, employee name, and salary for employees who have a rank within the top 3 for their department, sorted on department name and salary in descending order.


Solution Code:
SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM (
  SELECT departmentId, 
         salary, 
         name,
         dense_rank() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rank 
  FROM Employee
) as e
JOIN Department as d ON e.departmentId = d.id
WHERE e.rank <= 3
ORDER BY d.name, e.salary DESC
Output:
| Department | Employee | Salary |
| ---------- | -------- | ------ |
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top