230220

문제

https://leetcode.com/problems/department-top-three-salaries/description/

• department별 top 3 salary를 받는 사람 추출하기(많이 받는 순 기준)

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

IT부서의 top3 salary는 90000, 85000, 70000이므로 69000을 받는 janet은 제외됨.

풀이

SELECT Department
      , Employee
      , Salary
FROM (
    SELECT d.name Department
        , e.name Employee
        , e.salary Salary
        , DENSE_RANK() OVER (PARTITION BY d.name ORDER BY e.salary DESC) drank
    FROM Employee e
        INNER JOIN Department d ON e.departmentId = d.id
) r
WHERE drank <= 3

꼭 DENSE_RANK()를 써야한다. RANK()는 값이 같을 때는 같은 순위를 부여한 후 그 만큼 숫자를 건너뛰므로( ex. 1위 1위 3위)
숫자가 비지 않는 DENSE_RANK()를 써야한다. ( ex. 1위 1위 2위)