Walk the category tree and report its depth

Question

A catalogue team maintains categories as a parent-child tree in `catalog_categories`. Root categories have a `NULL` parent.

Using a recursive CTE, return `category_id`, `category_name`, `depth` (1 for roots, 2 for their children, and so on) and `path` — the names from the root down to that category

Accepted Answer

Step 1: Write the **anchor member**: every row whose `parent_id IS NULL` is a root, gets `depth = 1` and a `path` equal to its own name. Step 2: Write the **recursive member**: join `catalog_categories` back to the CTE on `t.category_id = c.parent_id` so each iteration attaches the children of the rows found so far. Step 3: Carry the accumulators forward — `t.depth + 1` increments the level and `t.path || ' > ' || c.category_name` appends to the breadcrumb. Step 4: Combine the two members with `UNION ALL`. Postgres repeats the recursive step until it produces no new rows. Step 5: Select from the CTE in the outer query. `WITH RECURSIVE` is required even though only one member recurses.

Walk the category tree and report its depth

More Recursive CTEs questions

Asked at Amazon