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

[LeetCode/SQL]

---

📌 1731. The Number of Employees Which Report to Each Employee

문제

풀이

-  리포트를 받는 직원의 아이디,이름,리포트 갯수,평균 나이를 출력

방법 I

SUBQUERY 사용

SELECT e.employee_id
      ,e.name
      ,sub.reports_count AS reports_count
      ,sub.average_age AS average_age
FROM (
        SELECT reports_to
              ,count(*)  AS reports_count
              ,ROUND(SUM(age)/COUNT(*)) AS average_age
        FROM Employees
        WHERE reports_to IS NOT NULL
        GROUP BY reports_to  ) AS sub
        ,Employees AS e

WHERE sub.reports_to=e.employee_id
ORDER BY e.employee_id

방법 II

SELFJOIN 사용

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 e2.employee_id
ORDER BY e2.employee_id