570 Managers with at Least 5 Direct Reports

To determine managers who have at least 5 direct reports, we need to analyze the Employee table by grouping employees under their respective managers.

Approach

1. Use a LEFT JOIN to self-join the Employee table:
   • We join the table on managerID and id to associate employees with their respective managers:

   FROM Employee AS e1
   LEFT JOIN Employee AS e2
   ON e1.managerID = e2.id
   

   • This ensures that every employee is matched with their corresponding manager.
2. Group by manager and count direct reports:
   • We use GROUP BY to count the number of employees under each manager:

   GROUP BY e1.managerID
   HAVING COUNT(*) >= 5
   

Conclusion

By structuring the query in this way, we can efficiently identify managers with 5 or more direct reports, which is useful for organizational analysis and HR reporting.