[MYSQL] The Number of Employees Which Report to Each Employee

The Number of Employees Which Report to Each Employee

Table: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
+-------------+----------+
employee_id is the column with unique values for this table.
This table contains information about the employees and the id of the manager they report to. Some employees do not report to anyone (reports_to is null).

문제
적어도 한명의 다른 직원이 그들에게 보고한다면 매니저로 간주한다.
모든 매니저의 id, 이름, 보고하는 직원 수, 보고자들의 평균연령을 가까운 정수로 반올림해서 보고하시오.

예제

Input: 
Employees table:
+-------------+---------+------------+-----+
| employee_id | name    | reports_to | age |
+-------------+---------+------------+-----+
| 9           | Hercy   | null       | 43  |
| 6           | Alice   | 9          | 41  |
| 4           | Bob     | 9          | 36  |
| 2           | Winston | null       | 37  |
+-------------+---------+------------+-----+
Output: 
+-------------+-------+---------------+-------------+
| employee_id | name  | reports_count | average_age |
+-------------+-------+---------------+-------------+
| 9           | Hercy | 2             | 39          |
+-------------+-------+---------------+-------------+
Explanation: Hercy has 2 people report directly to him, Alice and Bob. Their average age is (41+36)/2 = 38.5, which is 39 after rounding it to the nearest integer.

오답쿼리

select employee_id, name, 
        count(reports_to) reports_count, round(avg(age)) average_age
from employees
where reports_to in (select employee_id from employees)

내가 처음 쓴 오답쿼리를 다시보는데, 무슨 생각으로 이렇게 쓴건지 모르겠다.
아무런 전략없이 대충 쓴게 표가 난다 ㅎㅎ...

결과는 employee_id = 9 인 Hercy가 나와야 하는데, 엉뚱한 앨리스가 나왔다.
저 알수없는 where절이 그 원인이다.

정답쿼리

select e1.employee_id,
        e1.name,
        count(e2.reports_to) reports_count,
        round(avg(e2.age)) average_age
from employees e1 left join employees e2 on e1.employee_id=e2.reports_to
where e2.reports_to is not null
group by e2.reports_to
order by e1.employee_id

솔루션의 도움을 받았다.

1. join을 하되, 조인의 조건을 employee_id = reports_to 로 엮는다.
2. 엮이지 않은 데이터는 where 절에서 null 값을 제외해서 필터링한다.