Interview Questions/SQL/Full Ancestor Chain Per Employee

Full Ancestor Chain Per Employee

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

Medium

Full Ancestor Chain Per Employee

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

Examples

Example 1

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.

query.sql