[LeetCode] 185. Department Top Three Salaries

🔗 Problem Link

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

---

❔Thinking

• 각 부서별 임금 상위 3개를 상위 임금자라 할 때, 상위 임금자의 부서와 이름, 임금을 반환한다.

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

 

---

💻Solution

1. 서브쿼리를 활용한 풀이

SELECT
    D.name AS Department,
    E1.name AS Employee,
    E1.salary AS Salary
FROM Employee E1
JOIN Department D ON E1.departmentId = D.id
WHERE 3 > (
    SELECT COUNT(DISTINCT E2.salary)
    FROM Employee E2
    WHERE E2.departmentId = E1.departmentId
      AND E2.salary > E1.salary
)
ORDER BY D.name, E1.salary DESC

 

2. 윈도우 함수, WITH절을 활용한 풀이

WITH SalaryRank AS(
    SELECT E.*, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS EARN
    FROM Employee E
)
SELECT 
    D.name AS Department,
    S.name AS Employee, 
    S.salary AS Salary
FROM SalaryRank AS S
JOIN Department D ON S.departmentId = D.id
WHERE S.EARN <= 3

---

🗝️keypoint

1. WITH절은 CTE(Common Table Expression)은 테이블이나 뷰와 같이 임시 결과셋이다.
2. WITH절은 가독성과 재사용성을 높인다.