SQL data engineering interview problem. Difficulty: intermediate. Pattern: CTEs. About 12 minutes. Part of the Pro drill bank.
Using a recursive CTE on employees, return every employee in the reporting tree under manager employee_id = 1 (Alice), not including Alice herself. Columns: employee_id, name, manager_id, depth where depth=1 for direct reports. Order by depth, employee_id.
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
Interview-style drill: Recursive CTE for all reports under employee_id 1.
Using a recursive CTE on `employees`, return every employee in the reporting tree under manager `employee_id = 1` (Alice), not including Alice herself. Columns: `employee_id`, `name`, `manager_id`, `depth` where depth=1 for direct reports. Order by `depth`, `employee_id`.