HR wants a headcount view: for every employee, how many people report to them in total, counting both direct reports and everyone further down the chain. Use a recursive CTE to build each manager's full subtree, then count it.
Return EmployeeId, ManagerName, and TotalReports (0 for individual contributors), ordered by TotalReports descending (ties broken by EmployeeId ascending).
| Column | Type |
|---|---|
| EmployeeId | INTEGER (Primary Key) |
| ReportsTo | INTEGER (Foreign Key -> Employee.EmployeeId, nullable) |
Ready to take your skills to the next level? Enroll in our comprehensive Data Analyst Career Track to master SQL, Python, Excel, and Power BI.
Run your code to see the result here.