[LeetCode] 1731. The Number of Employees Which Report to Each Employee

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH report_employee AS (
    SELECT reports_to
        , COUNT(DISTINCT employee_id) AS reports_count
        , ROUND(AVG(age), 0) AS average_age
    FROM Employees
    GROUP BY reports_to
    HAVING reports_to IS NOT NULL
)

SELECT e.employee_id
     , e.name
     , re.reports_count
     , re.average_age
FROM Employees AS e
    LEFT JOIN report_employee AS re ON e.employee_id = re.reports_to
WHERE re.reports_count IS NOT NULL
  AND re.average_age IS NOT NULL
ORDER BY employee_id ASC