[LeetCode] 185. 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.

The result format is in the following example.

 

부서별 급여가 높은 3인을 출력하면 된다.

 

쿼리

SELECT 
    A.NAME AS DEPARTMENT,
    C.NAME AS EMPLOYEE,
    C.SALARY AS SALARY
FROM 
    (
        SELECT 
            DEPARTMENTID,
            NAME,
            DENSE_RANK() OVER (
                PARTITION BY DEPARTMENTID 
                ORDER BY SALARY DESC
            ) AS ranking,
            SALARY
        FROM 
            EMPLOYEE
    ) C
JOIN 
    DEPARTMENT A 
    ON C.DEPARTMENTID = A.ID
WHERE 
    C.ranking <= 3;

 

문제 분류가 HARD로 되어있긴 한데 RANK와 PARTITION BY를 사용할 줄 알면 크게 어렵지는 않은 것 같다.

 

 

 

 

 

 

 

문제 출처

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