Lowest Common Ancestor of Two Employees
Find the lowest common ancestor (LCA) of employee_id 8 (Henry) and employee_id 13 (Mia) — the deepest employee that appears in both of their manager chains. Walk both chains upward via recursive CTEs, intersect on employee_id, and pick the one with the smallest combined depth. Return ancestor_id, ancestor_name, depth_from_a, depth_from_b — exactly one row.
- Recursive CTE
- CTEs
- Joins
- Subqueries
- Filtering
Exercise brief
Understand the request
Identity governance engineer An approval-routing service must find the nearest shared manager of two employees.
Return
- Return one ancestor with its depth from both employees.
- Return exactly one deterministically selected row.
Constraints
- Walk both manager chains upward.
- Intersect on employee_id and choose the minimum combined depth.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERemployee_nameTEXTmanager_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Walk both chains upward independently with two recursive CTEs.
Hint 2
INNER JOIN on employee_id intersects the two chains; LIMIT 1 picks the deepest (smallest combined depth).
Hint 3
Henry (8) and Mia (13) both report to Emma (5) → LCA = Emma at depth 1 from each.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH RECURSIVE chain_a AS (SELECT employee_id, employee_name, manager_id, 0 AS d FROM employees WHERE employee_id = 8 UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, ca.d + 1 FROM employees e INNER JOIN chain_a ca ON e.employee_id = ca.manager_id), chain_b AS (SELECT employee_id, employee_name, manager_id, 0 AS d FROM employees WHERE employee_id = 13 UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, cb.d + 1 FROM employees e INNER JOIN chain_b cb ON e.employee_id = cb.manager_id) SELECT ca.employee_id AS ancestor_id, ca.employee_name AS ancestor_name, ca.d AS depth_from_a, cb.d AS depth_from_b FROM chain_a ca INNER JOIN chain_b cb ON ca.employee_id = cb.employee_id ORDER BY (ca.d + cb.d), ca.employee_id LIMIT 1;Why this works
LCA queries appear in permission systems ("smallest org unit covering these two users"), file-system tools ("common parent directory"), and graph analytics. The two-chain-and-intersect pattern is the standard SQL approach.
Success check
The deepest common ancestor of employees 8 and 13 is returned with correct distances.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| ancestor_id | ancestor_name | depth_from_a | depth_from_b |
|---|---|---|---|
| 5 | Emma Sales Mgr | 1 | 1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.