LeetCode - 185. Department Top Three Salaries (MySQL)

Hard, SQL - 윈도우함수, 인라인 뷰

RunTime : 887 ms

문제

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Return the result table in any order.

The result format is in the following example.

풀이

• SQLD 공부를 하면서 윈도우 함수와 인라인 뷰에 익숙해진게 도움이 많이 되었다.
• 인라인 뷰를 통해 각 부서 별 salary 상위 3위를 뽑아야한다.
  3명이 아닌 1, 2, 3위를 뽑아야함으로 DENSE_RANK()를 사용함에 주의하자.

SELECT DEPT_NAME AS Department, NAME as Employee, Salary FROM
(SELECT D.NAME AS DEPT_NAME, E.NAME, E.SALARY, 
DENSE_RANK() OVER(PARTITION BY D.NAME ORDER BY E.SALARY DESC) AS RK
FROM DEPARTMENT AS D JOIN EMPLOYEE AS E ON D.ID = E.DEPARTMENTID
) AS SUB
WHERE RK <= 3
ORDER BY DEPT_NAME