SQL | LeetCode 185. DENSE_RANK()로 부서별 Top 3 급여자 구하기

Table: 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.

 

Table: 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.

 

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.

The 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

 

Constraints:

• There are no employees with the exact same name, salary and department.

각 부서(department)에서 상위 3개의 “고유한” 급여를 받는 직원들을 구하는 SQL 문제입니다. 각 부서 안에서 따로 상위 3개 급여를 찾아야 합니다. 같은 급여를 여러 명이 받아도 고유 급여로는 한 번만 계산합니다.

예:

• 90000 → 1등
• 85000 → 2등
• 70000 → 3등

따라서 85000을 받는 사람이 두 명이라면, 둘 다 2등 안에 해당되어 포함됩니다.

 

DENSE_RANK()사용

• 부서별로 나누고 (PARTITION BY departmentId)
• 급여 기준 내림차순으로 순위 매김 (ORDER BY salary DESC)
• 고유 급여 기준으로 순위 매겨서 공동 순위도 처리 가능!

select *, 
    dense_rank() over (PARTITION BY departmentId order by salary desc) as rk 
    from employee

부서 이름 붙이기

select d.name as Department, e.name as Employee, e.salary as Salary
from (
    select *, 
    dense_rank() over (PARTITION BY departmentId order by salary desc) as rk 
    from employee
    ) e
join Department d on e.departmentId = d.id

• Employee 테이블에 departmentId가 있으므로 Department 테이블과 JOIN

3위 이내만 필터링

select d.name as Department, e.name as Employee, e.salary as Salary
from (
    select *, 
    dense_rank() over (PARTITION BY departmentId order by salary desc) as rk 
    from employee
    ) e
join Department d on e.departmentId = d.id
where rk <= 3

 

CTE (Common Table Expression)를 사용해서도 동일한 결과를 얻을 수 있습니다.

with rkcte as (
    select *, 
    dense_rank() over (PARTITION BY departmentId order by salary desc) as rk 
    from employee e
    )
select d.name as Department, r.name as Employee, r.salary as Salary
from rkcte r
join Department d on r.departmentId = d.id
where r.rk <= 3