106. The Number of Employees Which Report to Each Employee

[문제]

https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/description/

 

[풀이]

1. 매니저 아이디(reports_to) 별로 그룹화 한 후 agg 함수를 통해 열이름과 집계함수를 동시에 적용해준다.

2. reports_to 는 count 함수를, age는 mean 함수를 적용하고, 예전에 살펴봤듯이 판다스는 반올림 규칙(0.5일 때는 짝수로 처리)이 따로 있기 때문에 작은 수를 더해줘서 이부분을 커버해준다. 열이름은 각각 reports_count, average_age 로 설정한다.

3. rename 함수를 통해 reports_to를 employee_id로 변경하여 병합을 쉽게 만들고, 출력할 컬럼만 선택한 뒤 sort_values 함수로 employee_id 를 정렬해서 반환하자.

 

Pandas

import pandas as pd

def count_employees(employees: pd.DataFrame) -> pd.DataFrame:
    t1 = employees.groupby('reports_to').agg(
        reports_count=('reports_to', 'count')
        , average_age=('age', lambda x: round((x+0.0001).mean()))
    ).reset_index().rename(columns={'reports_to':'employee_id'})
    return t1.merge(employees)[['employee_id', 'name', 'reports_count', 'average_age']].sort_values('employee_id')

 

SQL

SELECT 
    e2.employee_id
    , e2.name
    , COUNT(e1.employee_id) as reports_count
    , ROUND(AVG(e1.age)) as average_age
FROM Employees e1
JOIN Employees e2
    ON e1.reports_to = e2.employee_id
GROUP BY employee_id
ORDER BY employee_id