[LeetCode] 184. Department Highest Salary

🔗 Problem Link

https://leetcode.com/problems/department-highest-salary/description/

---

❔Thinking

Employee 테이블과 Department 테이블이 주어질 때, 각 부서별 최고 연봉을 받는 사람의 정보를 담은 테이블을 반환한다.

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+​

---

💻Solution

1. 조인과 서브쿼리를 이용한 풀이

SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN (
    SELECT departmentId, MAX(salary)
    FROM Employee
    GROUP BY departmentId
);

 

 

2. 윈도우 함수 (DENSE_RANK()) 를 활용한 풀이

WITH SalaryRank AS (
  SELECT 
    e.*,
    DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr
  FROM Employee e
)
SELECT 
  d.name AS Department,
  s.name AS Employee,
  s.salary AS Salary
FROM SalaryRank s
JOIN Department d ON s.departmentId = d.id
WHERE s.dr = 1;

---

🗝️keypoint

1. PARTITION BY를 사용해 departmentID를 기준으로 salary의 순위를 매기고, s.dr = 1로 1위인 값만 가져온다.
2. 윈도우함수를 내부 최적화를 통해, 데이터가 많은 경우 IN 연산보다 효율적일 수 있다.