107. Primary Department for Each Employee

[문제]

https://leetcode.com/problems/primary-department-for-each-employee/description/

 

[풀이]

1. 집계함수를 그룹별로 적용할 때 전체 행을 유지하면서 집계해주는 transform 함수를 사용

2. primary_flag가 'Y' 이거나 소속부서가 하나인 행들만 필터링

3. employee_id와 department_id 컬럼만 반환

 

Pandas

import pandas as pd

def find_primary_department(employee: pd.DataFrame) -> pd.DataFrame:
    employee['department_cnt'] = employee.groupby('employee_id')['department_id'].transform('count')
    return employee[(employee['primary_flag'] == 'Y') | (employee['department_cnt'] == 1)][['employee_id', 'department_id']]

 

SQL

SELECT
    employee_id
    , department_id
FROM Employee
WHERE primary_flag = 'Y'
OR employee_id IN (SELECT employee_id
                   FROM Employee
                   GROUP BY employee_id
                   HAVING COUNT(department_id) = 1)