# $1731. 每位经理的下属员工数量
# https://leetcode-cn.com/problems/the-number-of-employees-which-report-to-each-employee/

# SQL架构
Create table If Not Exists Employees(employee_id int, name varchar(20), reports_to int, age int);
Truncate table Employees;
insert into Employees (employee_id, name, reports_to, age) values ('9', 'Hercy', null, '43');
insert into Employees (employee_id, name, reports_to, age) values ('6', 'Alice', '9', '41');
insert into Employees (employee_id, name, reports_to, age) values ('4', 'Bob', '9', '36');
insert into Employees (employee_id, name, reports_to, age) values ('2', 'Winston', null, '37');

# Write your MySQL query statement below
select
    e2.employee_id,
    e2.name,
    count(*) 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,
    # sql_mode=only_full_group_by
    e2.name
order by
    e2.employee_id;

# clean-up
drop table Employees;