119. Department Top Three Salaries

[문제]

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

 

[풀이]

1. 부서 테이블과 병합한 후

2. 부서별로 salary의 순위를 계산한다. 이때 dense 옵션으로 1, 2, 2, 3 처럼 숫자의 누락이 없게, 같은 값은 같은 수위로 배정한다.

3. 순위가 4보다 작은 애들만 필터링한다.

 

Pandas

import pandas as pd

def top_three_salaries(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    department = department.rename(columns={'id': 'departmentId', 'name': 'department'})
    merged = employee.merge(department)
    merged['rank'] = merged.groupby('department')['salary'].transform(lambda x: x.rank(method='dense', ascending=False))
    return merged[merged['rank'] < 4][['department', 'name', 'salary']].rename(columns={'name':'employee'})

 

SQL

WITH joined AS (
    SELECT 
        d.name AS department
        , e.name AS employee
        , salary
    FROM Employee e
    JOIN Department d
        ON e.departmentId = d.id
),
ranked AS (
    SELECT 
        *
        , DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_ranking
    FROM joined
)
SELECT 
    department
    , employee
    , salary
FROM ranked
WHERE salary_ranking <= 3