1731. The Number of Employees Which Report to Each Employee
SQL 50 · Advanced Select and Joins (day 5). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One manager.
2. Approach
Self join: e.reports_to = m.employee_id, GROUP BY m, COUNT(*), ROUND(AVG(e.age)).
3. The trap
Order by employee_id. ROUND with no decimals.Order by employee_id. AVG of integers is numeric, so ROUND rounds 0.5 up. ROUND on a double would round half to even.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
SELECT m.employee_id, m.name,
COUNT(*) AS reports_count,
ROUND(AVG(e.age)) AS average_age
FROM Employees e
JOIN Employees m ON e.reports_to = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY m.employee_id;