[Leetcode] Department Highest Salary

문제 링크

정답

WITH Max_Price AS (
    SELECT
        departmentId,
        MAX(salary) max_price
    FROM Employee
    GROUP BY departmentId
)

SELECT
    D.name Department,
    E.name Employee,
    E.salary Salary
FROM Employee E
JOIN Max_Price MP
ON E.departmentId = MP.departmentId AND E.salary = MP.max_price
JOIN Department D
ON E.departmentId = D.id

✅ Point

• 3개의 table JOIN
  • Employee
  • Max_Price (부서별 최대 salary)
  • Department

다른 풀이

• rank를 이용한 풀이

  SELECT 
    Department, Employee, Salary
  FROM (
    SELECT
        D.name Department,
        E.name Employee,
        E.salary Salary,
        RANK() OVER (PARTITION BY E.departmentId ORDER BY E.salary DESC) rank_
    FROM Employee E
    JOIN Department D
    ON E.departmentId = D.id
  ) t
  WHERE rank_ = 1;