[SQL] LeetCode: 184. Department Highest Salary (윈도우 함수)

184. Department Highest Salary

 

문제

 

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

 

 

풀이1)  윈도우 함수 MAX( ) + FROM절 서브쿼리 사용

 

SELECT department
     , employee
     , salary
FROM(
    SELECT d.name AS department
        , e.name AS employee
        , e.salary
        , MAX(salary) OVER (PARTITION BY e.departmentid) AS max_salary
    FROM employee e
        INNER JOIN department d  ON e.departmentid = d.id
)ms
WHERE ms.salary = ms.max_salary

 

 

풀이2) 윈도우 함수 RANK( ) + FROM절 서브쿼리 사용

 

SELECT department
     , employee
     , salary
FROM(
    SELECT d.name AS department
        , e.name AS employee
        , e.salary
        , RANK() OVER (PARTITION BY e.departmentid ORDER BY e.salary DESC) AS max_salary
    FROM employee e
        INNER JOIN department d  ON e.departmentid = d.id
)ms
WHERE ms.max_salary = 1

 

 

풀이3) 윈도우 함수 DENSE_RANK( ) + WITH문 사용

 

WITH ms AS (
    SELECT d.name AS department
        , e.name AS employee
        , e.salary
        , DENSE_RANK() OVER (PARTITION BY e.departmentid ORDER BY e.salary DESC) AS max_salary
    FROM employee e
        INNER JOIN department d  ON e.departmentid = d.id
)

SELECT department
     , employee
     , salary
FROM ms
WHERE ms.max_salary = 1

 

 

# WHERE절에서는 윈도우 함수 사용할 수 없음 -> 서브쿼리 이용

# 가독성 면에서 FROM절 서브쿼리보다 WITH문 사용이 좋아보임

# Runtime이 가장 짧은 풀이방법은 3번(894 ms)