185. Department Top Three Salaries

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

-- 풀이
-- Output을 보면 Rank가 아닌 Dense rank를 써야 함을 알 수 있음.
-- 물론 다른 방법도 많을 것...

with salary_rank as (
  select d.name as Department
       , e.name as Employee
       , e.salary as Salary
       , dense_rank() over(partition by d.id order by e.salary desc) as ranking
    from Employee e
    left join Department d
      on e.departmentId = d.id
)
select Department
     , Employee
     , Salary
  from salary_rank
  where ranking <= 3

 

- 해설

rank 관련 함수에 대해 이해하고 있고, 사용할 수 있다면 크게 어려운 문제는 아니었다.

 

주의할 점은, Output에서도 확인할 수 있듯 salary가 같은 경우엔 같은 rank로 두고, 그 다음은 rank 건너뛰는거 없이 다음 rank로 간다는 것.

 

예를 들어 salary가 250, 200, 200, 100, 100, 50 이렇게 있을 경우 각각 1, 2, 3, 4, 5, 6등 / 1, 2, 2, 4, 4, 6등 으로 매기는 것이 아니라, 1, 2, 2, 3, 3, 4 등 으로 rank를 매긴다.

 

이를 위해 row_number(), dense_rank(), rank() 간 차이를 확실하게 알고 있는 것이 좋겠다.