문제

source: LeetCode

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 an SQL query to find the employees who are high earners in each of the departments.
Return the result table in any order.

제출답안(MySQL)

WITH 
    emp_salary_rnk_by_dpt AS(
        SELECT
            *, 
            DENSE_RANK()OVER(PARTITION BY departmentid ORDER BY SALARY DESC) AS sal_rnk_in_dpt
        FROM 
            employee
    )

SELECT 
    d.name AS Department, 
    top3_sal_emp_by_dept.name AS Employee,
    salary AS Salary
FROM
    (
        SELECT 
            *
        FROM
            emp_salary_rnk_by_dpt
        WHERE 
            sal_rnk_in_dpt <= 3) AS top3_sal_emp_by_dept
    LEFT JOIN 
        department AS d ON top3_sal_emp_by_dept.departmentid = d.id

풀이(MySQL)

부서별 salary ranking 데이터를 가진 (DENSE RANK) CTE 생성

WITH 
    emp_salary_rnk_by_dpt AS(
        SELECT
            *, 
            DENSE_RANK()OVER(PARTITION BY departmentid ORDER BY SALARY DESC) AS sal_rnk_in_dpt
        FROM 
            employee
    )

>> Output 보기를 보면 2위에 동점자 2명이 있다. 따라서 공동 2위의 경우 다음 순위는 4위가 아니라 3위가 되어야함

>> 따라서 RANK() 함수가 아니라 DENSE_RANK 함수를 사용하였

부서 내 Salary ranking 3위만 불러오는 서브쿼리를 생성 후 코드 작성

SELECT 
    d.name AS Department, 
    top3_sal_emp_by_dept.name AS Employee,
    salary AS Salary
FROM
    (
        SELECT 
            *
        FROM
            emp_salary_rnk_by_dpt
        WHERE 
            sal_rnk_in_dpt <= 3) AS top3_sal_emp_by_dept
    LEFT JOIN 
        department AS d ON top3_sal_emp_by_dept.departmentid = d.id

문제 바로가기(MySQL)

728x90