185. Department Top Three Salaries

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

• 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.

• 부서별 상위 3명 고소득 직원 구하기

• 오답

SELECT d.name Department
     , e.name Employee
     , salary Salary
FROM employee e
    JOIN department d ON e.departmentID = d.id
ORDER BY salary DESC
LIMIT 3

• 정답

SELECT Department, Employee, Salary
FROM (
    SELECT d.name AS Department
         , e.name AS Employee
         , e.salary AS Salary
         , DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rn
    FROM Employee e
         INNER JOIN Department d ON e.departmentId = d.id
) df
WHERE rn <= 3

Lesson & Learned

서브쿼리 활용하자.
예시에 IT부서는 1, 2, 2, 3으로 조회되었다. ROW_NUMBER(), RANK( ), DENSE_RANK( ) 중 DENSE_RANK 사용
Sales부서는 3명 이하인 2명으로 구성되었다. WHERE절에서 순위를 3이하로 조건을 준다.