Reviewed by Aditya Kumar · Last reviewed 2026-03-24
WITH RECURSIVE hierarchy AS (SELECT employee_id, manager_id, 1 AS level FROM employees WHERE employee_id = 1 UNION ALL SELECT e.employee_id, e.manager_id, h.level+1 FROM employees e JOIN hierarchy h ON e.manager_id = h.employee_id) SELECT * FROM hierarchy. **Anchor**: CEO...
WITH RECURSIVE hierarchy AS (SELECT employee_id, manager_id, 1 AS level FROM employees WHERE employee_id = 1 UNION ALL SELECT e.employee_id, e.manager_id, h.level+1 FROM employees e JOIN hierarchy h ON e.manager_id = h.employee_id) SELECT * FROM hierarchy. Anchor: CEO (id=1). Recurse: Join employees to current level. Cycles: Add cycle detection (path array). Limit: SET max_recursion (e.g., 100). Index: manager_id.
Red Flag: No recursion limit—infinite loop on bad data. Pro-Move: 'We added cycle detection with ARRAY_AGG(employee_id) and EXCLUDE to prevent circular manager references from corrupting the hierarchy.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.