Full Ancestor Chain Per Employee

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a Staff table representing a management hierarchy. Write a SQL query that, for every non-root employee, lists each of their ancestors (managers up the chain to the root) along with how many levels above that ancestor is. Return (employee_id, employee_name, ancestor_id, ancestor_name, levels_above) ordered by employee_id, then levels_above. Do NOT include the root employee in the output (they have no ancestors). Root employees also should not appear as an employee row. Table: Staff

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
manager_id	INT	NULL if this is the root

Database Schema (Inferred)

Staff

Column Name	Example Value
id	1
name	CEO
manager_id	NULL

Example

Staff

id	name	manager_id
1	CEO	NULL
2	VP_Eng	1
3	VP_Sales	1
4	Manager_A	2
5	Manager_B	2
6	Engineer_X	4
7	Engineer_Y	4
8	Sales_Rep	3

Output

employee_id	employee_name	ancestor_id	ancestor_name	levels_above
2	VP_Eng	1	CEO	1
3	VP_Sales	1	CEO	1
4	Manager_A	2	VP_Eng	1
4	Manager_A	1	CEO	2
5	Manager_B	2	VP_Eng	1
5	Manager_B	1	CEO	2
6	Engineer_X	4	Manager_A	1
6	Engineer_X	2	VP_Eng	2
6	Engineer_X	1	CEO	3
7	Engineer_Y	4	Manager_A	1
7	Engineer_Y	2	VP_Eng	2
7	Engineer_Y	1	CEO	3
8	Sales_Rep	3	VP_Sales	1
8	Sales_Rep	1	CEO	2

Explanation:

Anchor: every non-root employee paired with their direct manager. Recurse: keep climbing to the manager's manager, incrementing level each time, until reaching the root.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.