HR wants each employee's complete reporting path from themselves up to the top of the org, plus how many levels above them that is. Use a recursive CTE over Employee.ReportsTo.
Return EmployeeId, EmployeeName (FirstName and LastName joined with a space), ManagementChain (the path from the employee up through each successive manager, separated by ' -> '), and ChainDepth (0 for the top of the org), ordered by EmployeeId ascending.
| Column | Type |
|---|---|
| EmployeeId | INTEGER (Primary Key) |
| FirstName | TEXT |
| LastName | TEXT |
| 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.