SQL > LeetCode 185. Department Top Three Salaries (MySQL)

SQL 50에 해당하는 문제도 슬슬 다 풀어가고있다.

이 다음에는 프로그래머스의 PCSQL을 도전해볼 생각이다.

문제

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

Return the result table in any order.

 

회사의 각 부서에서 누가 가장 많은 돈을 벌고있는지 경영진은 관심이 많다.

한 부서의 고소득자는 해당 부서의 상위 3개의 급여로 계산한다.

각 부서에서 고소득 직원을 찾아야한다.

 

테이블 & 예제

[Employee]

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference column) 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.

 

[Department]

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

 

[Example]

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  |
+------------+----------+--------+

 

풀이

rank를 매기는 방법으로 진행했다.

우선 각 부서별로 소득에 대해서 rank를 매겨준다. 주의할점은 그냥 rank를 매기면 동일 값인 경우에 다음 rank가 중복순위도 처리해서 나온다는 것이다.

조금 더 알아보기 위해서 rank, dense_rank, row_number에 대해서 정리하겠다.

 

[RANK]

동일한 값이면 중복 순위를 부여하고 다음 순위부터는 해당 개수만큼 건너뛴다.

예를 들어 100,1000,1000,1001 이렇게 값이 있으면 1,2,2,4 이렇게 순위가 매겨진다는 의미이다.

 

[DENSE_RANK]

동일한 값이면 중복 순위를 매기나, 다음 순위는 중복 순위에 상관없이 순차적으로 순위를 매긴다.

예로 본다면 100,1000,1000,1001 이렇게 값이 있으면 1,2,2,3 이렇게 순위가 매겨진다는 의미이다.

 

[ROW_NUMBER]

중복 필요없다. 그냥 순차적으로 순위를 반환한다.

예를 들어 100,1000,1000,1001 이렇게 값이 있으면 1,2,3,4 이렇게 순위가 매겨진다는 의미이다.

 

자 그러면 우리에게 필요한 것은 DENSE_RANK이다. 부서별로 rank를 매기는데 이떄 내림차순으로 매겨야한다. (비싼거를 볼거니까)

또한 DENSE_RANK로 매기면 rank별로 사람의 수에 상관없이 계층을 나눌 수 있어 후에 추가적인 계산이 필요없다.

SELECT *,
    DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY Salary DESC) AS rk
    FROM Employee

 

이렇게 순위를 매겼다면 이제 부서의 이름을 달아주기위해서 JOIN을 진행한다. 그리고나서 rank가 1,2,3인 경우만 가져오면 부서별 고소득자를 추려낼 수 있다.

WITH
CTE AS (
    SELECT *,
    DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY Salary DESC) AS rk
    FROM Employee
),
CTE2 AS (
    SELECT d.name AS Department, c.name AS Employee, c.salary AS Salary,c.rk
    FROM CTE AS c
    JOIN Department AS d ON c.departmentId = d.id
    ORDER BY d.name,c.rk
)
SELECT Department, Employee, Salary
FROM CTE2
WHERE rk <4