[SQL풀이][LeetCode][easy] 1731. The Number of Employees Which Report to Each Employee

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

The Number of Employees Which Report to Each Employee - LeetCode

 

문제설명

Employees 테이블을 참고하여 1명 이상의 report를 받는 manager의 정보 및 report건수, reporter들의 평균나이를 구하여라.

 

 

문제이해 

- self join 을 통해 어떤 manager가 보고를 받고 있는지 파악하는 것이 문제풀이의 핵심

- reports_to 와 employee_id 를 이용해 selt join후 report건수, reporter 평균나이 계산 진행

 

 

풀이코드

SELECT
  a.employee_id,
  a.name,
  COUNT(a.employee_id) AS reports_count,
  ROUND(AVG(b.age), 0) AS average_age
FROM Employees as a
INNER JOIN Employees as b
ON b.reports_to = a.employee_id
GROUP BY a.employee_id
ORDER BY a.employee_id