LeetCode 1731: The Number of Employees Which Report to Each Employee

Explanation:

To solve this problem, we need to query the information about managers, including their IDs, names, the number of employees reporting directly to them, and the average age of those employees.

We can achieve this by joining the Employees table with itself on the employee_id and reports_to columns to get the required information.

1. Join the Employees table with itself on employee_id = reports_to.
2. Group the result by the manager's employee_id and name.
3. Count the number of employees reporting directly to each manager.
4. Calculate the average age of the employees reporting to each manager.
5. Return the result table ordered by employee_id.

The time complexity of this solution is O(n^2) where n is the number of rows in the Employees table. The space complexity is O(n) to store the result.

:

# Write your Java solution here
SELECT e1.employee_id, e1.name, 
       COUNT(e2.employee_id) AS reports_count, 
       ROUND(AVG(e2.age), 0) AS average_age
FROM Employees e1
LEFT JOIN Employees e2 ON e1.employee_id = e2.reports_to
GROUP BY e1.employee_id, e1.name
HAVING reports_count > 0
ORDER BY e1.employee_id;