[코드카타] SQL 45 The Number of Employees Which Report to Each Employee

[코드카타] SQL 45 The Number of Employees Which Report to Each Employee

45. The Number of Employees Which Report to Each Employee
https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/

For this problem, we will consider a manager an employee who has 
at least 1 other employee reporting to them.
Write a solution to report the ids and the names of all managers, 
the number of employees who report directly to them, and the average 
age of the reports rounded to the nearest integer.
Return the result table ordered by employee_id.

SELECT e1.employee_id, e1.name, 
		count(e2.employee_id) reports_count, 
        round(avg(e2.age),0) average_age
FROM EMPLOYEES e1 join employees e2 on e1.employee_id=e2.reports_to
group by e1.employee_id
order by e1.employee_id

Self Join을 활용한 방법!
여기서!! 중요한 부분은 Join 할 때 e1.employee_id=e2.reports_to 을 묶는 것!
이렇게 묶으면 manager와 그에게 보고한 인원들을 확인 할 수 있다!
그렇기 때문에 Join 후의 e1.employee_id 은 매니저 id가 되는 것이고,
count(e2.employee_id) 는 보고한 수 가 되는 것이다.