[LeetCode] 185. Department Top Three Salaries

Posted on | In

Problem

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

1
2
3
4
5
6
7
8
9
10
11
+----+-------+--------+--------------+
| 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            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

1
2
3
4
5
6
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

1
2
3
4
5
6
7
8
9
10
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

Explanation

  1. First INNER JOIN Employee table and Department table by the DepartmentId.

  2. Then in order to return the top 3 Salary in the same department, we notice that the number of Salary is greater than the top 3 salary is less than 3. For example, there are only 2 salary that is higher than the third highest salary person; there are only 1 salary that is higher than the second highest salary person; there are 0 salary that is higher than the first highest salary person. So, in the WHERE query, we put () < 3. Inside the paranthesis, we return a subquery with SELECT count(DISTINCT Salary) from the Salary table. Inside this subquery, we match the subquery’s DepartmentId and choose only subquery’s Salary greater than the maching Salary.

  3. Finally we return the selected column.

Solution

1
2
3
4
5
6
7
8
9
10
# Write your MySQL query statement below
SELECT d1.Name as Department, e1.Name as Employee, e1.Salary as Salary
FROM Employee as e1
INNER JOIN Department as d1
ON e1.DepartmentId = d1.Id
WHERE ( SELECT count(DISTINCT e2.Salary)
        FROM Employee as e2
        WHERE e2.DepartmentId = e1.DepartmentId AND e2.Salary > e1.Salary
      ) < 3
ORDER BY d1.Name, e1.Salary DESC;