[LeetCode] 185. Department Top Three Salaries

😎풀이

1. RankdedSalaries라는 임시 테이블 생성
   1-1. PARTITION BY를 사용하여 각 부서별로 연봉 순위(SalaryRank)를 매기고, 모든 행을 유지
2. RankedSalaries 테이블에서 SalaryRank에 따라 부서 별 상위 3개의 행만을 반환

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