Complex SQL and Recursion | Dataford Interview Questions

Explain how you would handle complex SQL queries, including cases that involve recursion.

Write a PostgreSQL query that expands the reachable reporting hierarchy from employees whose manager is NULL. Exclude employees whose manager cannot be reached from a root, and protect the query from revisiting an employee in the same path.

Output

One row per reachable employee with employee_id, employee_name, root_employee_id, depth, and hierarchy_path
depth is 0 for each root
Order by hierarchy traversal from each root, with roots ordered by root_employee_id

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee display name
manager_id	INT	Employee ID of the direct manager

Your question is Complex SQL and Recursion. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Explain how you would handle complex SQL queries, including cases that involve recursion.

Output

One row per reachable employee with employee_id, employee_name, root_employee_id, depth, and hierarchy_path
depth is 0 for each root
Order by hierarchy traversal from each root, with roots ordered by root_employee_id

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee display name
manager_id	INT	Employee ID of the direct manager

Your question is Complex SQL and Recursion. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.