[Leetcode/SQL] 185. Department Top Three Salaries

문제는 여기서 확인할 수 있습니다.

 

Table: Employee

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

 

Table: Department

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

회사별로 가장 월급이 큰 순으로 3순위까지, 동일한 월급을 받는 사람이 2명 이상일 경우 각 사원을 모두 포함하여 select하라. 순서는 무관하다.

 

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

 

코드가 꽤 복잡해서 discussion 섹션의 코드들을 참고했다.

# case 1

- where, and 구문 : id와 departmentId를 맞추고, 두 사원의 월급을 비교합니다.

- distinct e2.salary라고 함으로써 월급이 같은 사원이 여러 명이 있어도 하나의 등수로 취급할 수 있도록, 예를 들어 1,2,3,3위가 존재할 때 3위 한 명을 배제하지 않고 모두 고려할 수 있도록 합니다.

# case 2

- dense_rank() + partition by~orderby~ : 동일 점수 동일 순위 취급 + column별로 구분해서 순위 매기기

 

Code:

# case 1
select d.name as department, e.name as Employee, e.salary 
from Department d, Employee e, Employee e2
where d.id = E.departmentId and e.departmentId = e2.departmentId and e.salary <= e2.salary
group by d.id, e.name having count(distinct e2.salary) <= 3;


# case 2
select d.name as Department, a.name as Employee, a.salary as Salary 
from (
select e.*, dense_rank() over (partition by departmentId order by salary desc) as DeptPayRank 
from Employee e 
) a
join Department d
on a.departmentId = d.Id
where a.DeptPayRank <=3;