SQL Recursive Hierarchy

The same WITH RECURSIVE pattern from the CTEs series — this is where it shows up as a named interview question.

What & Why

"Walk the org chart," "find all descendants of a category," "get a comment's full reply thread" — these are all the identical recursive CTE pattern from the CTEs lesson series, just described in interview language.

See How It Works

BUSINESS QUESTION

Product wants a feature-to-feedback hierarchy built from product.feedback.feature_id.

id	name	description	team	released_at	deprecated_at
1	Activation Checklist	Guides new users through setup	growth	2024-01-20	NULL
2	CSV Export	Exports report data	platform	2024-02-14	NULL
3	Invite Nudges	Prompts workspace collaboration	growth	2024-03-05	NULL
4	Legacy Dashboard	Original reporting surface	analytics	2023-08-10	2024-06-01

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

WITH RECURSIVE nodes AS (
  SELECT
    'feature:' || f.id AS node_id,
    NULL::text AS parent_id,
    f.name AS node_name,
    'feature'::text AS node_type
  FROM product.features f
  UNION ALL
  SELECT
    'feedback:' || fb.id,
    'feature:' || fb.feature_id,
    'rating ' || fb.rating,
    'feedback'::text
  FROM product.feedback fb
  WHERE fb.feature_id IS NOT NULL
),
hierarchy AS (
  SELECT node_id, parent_id, node_name, node_type, 0 AS depth
  FROM nodes
  WHERE parent_id IS NULL
  UNION ALL
  SELECT child.node_id, child.parent_id, child.node_name, child.node_type, parent.depth + 1
  FROM nodes child
  JOIN hierarchy parent ON child.parent_id = parent.node_id
)
SELECT node_id, parent_id, node_name, node_type, depth
FROM hierarchy
ORDER BY SPLIT_PART(COALESCE(parent_id, node_id), ':', 2)::int, depth, node_id;

RESULT — features and their feedback children

node_id	parent_id	node_name	node_type	depth
feature:1	NULL	Activation Checklist	feature	0
feedback:1	feature:1	rating 5	feedback	1
feedback:3	feature:1	rating 4	feedback	1
feature:2	NULL	CSV Export	feature	0
feedback:2	feature:2	rating 4	feedback	1
feature:3	NULL	Invite Nudges	feature	0
feedback:4	feature:3	rating 3	feedback	1
feature:4	NULL	Legacy Dashboard	feature	0

The recursive hierarchy starts with feature roots and attaches each feedback row at depth 1.

Advanced business practice