Employee hierarchy under Alice

SQL data engineering interview problem. Difficulty: intermediate. Pattern: CTEs. About 12 minutes. Part of the Pro drill bank.

In employees, each person points to their manager through manager_id. A whole company forms a tree: managers have reports, and those reports can have their own reports. To walk down such a tree you need a recursive CTE, a query that calls itself. Find everyone in the reporting tree under the manager with employee_id = 1 (Alice), at any depth. Do not include Alice herself. Return employee_id, name, manager_id and depth, where depth is 1 for direct reports, 2 for their reports, and so on. Sort by depth, then employee_id.

Requirements

• Start with the direct reports of employee 1 (depth 1).
• Repeatedly add the reports of the people already found, with depth plus 1.
• Do not include Alice (employee 1).
• Return employee_id, name, manager_id and depth, sorted by depth and employee_id.

Constraints

• The table is employees.
• Use WITH RECURSIVE. It has an anchor part (the starting rows) and a recursive part (joined back to the CTE itself), combined with UNION ALL.
• Return only these 4 columns, in this order: employee_id, name, manager_id, depth.

Examples

Input: employees employee_id | name | manager_id 1 | Alice Chen | NULL 2 | Bob Kumar | 1 3 | Dan Park | 1 4 | Eve Ng | 2 5 | Omar Ali | 1 6 | Max Reed | 5 Output: employee_id | name | manager_id | depth 2 | Bob Kumar | 1 | 1 3 | Dan Park | 1 | 1 5 | Omar Ali | 1 | 1 4 | Eve Ng | 2 | 2 6 | Max Reed | 5 | 2 Why this passes: Direct reports of Alice are depth 1. Eve reports through Bob and Max through Omar at depth 2.

Topics: lakebench, sql, recursive, cte.

More SQL interview questions · All interview problems · Learn data engineering