SQL problem - The Number of Employees Which Report to Each Employee

Problem

Query

The idea is to do the inner join on the table itself first in order to get the following table. Each row will contain the manager id and the employee id that the manager is supervising.

SELECT 
    *
FROM
    Employees AS e1
INNER JOIN
    Employees AS e2
    ON
        e1.employee_id = e2.reports_to

Result:

employee_id	name	reports_to	age	employee_id	name	reports_to	age
9	Hercy	null	43	6	Alice	9	41
9	Hercy	null	43	4	Bob	9	36

After we apply the necessary aggregate functions to get the final result

SELECT 
    e1.employee_id,
    e1.name,
    COUNT(e2.employee_id) AS 'reports_count',
    ROUND(AVG(e2.age * 1.0), 0) AS 'average_age'
FROM
    Employees AS e1
INNER JOIN
    Employees AS e2
    ON
        e1.employee_id = e2.reports_to
GROUP BY
    e1.employee_id, e1.name
ORDER BY
    e1.employee_id