Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Recursive CTE for path: WITH RECURSIVE cte AS (SELECT id, parent_id, CAST(id AS VARCHAR) path FROM tree WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, cte.path||'-'||t.id FROM tree t JOIN cte ON t.parent_id=cte.id) SELECT * FROM cte. Flat: SELECT node_id, parent_id...
Recursive CTE for path: WITH RECURSIVE cte AS (SELECT id, parent_id, CAST(id AS VARCHAR) path FROM tree WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, cte.path||'-'||t.id FROM tree t JOIN cte ON t.parent_id=cte.id) SELECT * FROM cte. Flat: SELECT node_id, parent_id ORDER BY parent_id NULLS FIRST. Handle root; depth limit for recursion.
Red Flag: Infinite recursion on cycles. Pro-Move: 'We add depth in CTE; LIMIT recursion for sanity; index parent_id for performance.'
According to DataEngPrep.tech, this is one of the most frequently asked General/Other interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.