Write a recursive CTE for an employee–manager hierarchy.

Short answer: Anchor member selects the root manager; recursive member joins employees whose ManagerId equals the CTE EmpId. UNION ALL combines them. Watch MAXRECURSION.

Sample solution

WITH Hierarchy AS (
    SELECT EmpId, ManagerId, Name, 0 AS Lvl
    FROM Employees
    WHERE ManagerId IS NULL   -- root
    UNION ALL
    SELECT e.EmpId, e.ManagerId, e.Name, h.Lvl + 1
    FROM Employees e
    INNER JOIN Hierarchy h ON e.ManagerId = h.EmpId
)
SELECT * FROM Hierarchy
ORDER BY Lvl, Name
OPTION (MAXRECURSION 100);

Edge cases to mention

• Cycles in hierarchy
• Multiple roots
• Deep trees hitting recursion limit

Say “anchor + recursive member + termination” — that structure is the interview checklist.