Category Tree with Depth — Recursive CTEs in SQL

The problem

Brightlane's product catalog stores categories in a two-level hierarchy. Root categories have no parent on record.

Write a query to return the ID, name, and depth level for every category in the hierarchy.

Assumptions:

The categories table has one row per category with an id, a name, and a parent_id.
A root category has a missing parent_id. A subcategory has a parent_id that references another category's id.
Every category appears in the result exactly once. Root categories carry depth 1; subcategories whose parent_id references a root carry depth 2.

Output:

One row per category, with columns id, name, and depth.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH RECURSIVE
  cat_tree AS (
    SELECT
      id,
      name,
      parent_id,
      1 AS depth
    FROM
      categories
    WHERE
      parent_id IS NULL
    UNION ALL
    SELECT
      c.id,
      c.name,
      c.parent_id,
      ct.depth + 1
    FROM
      categories c
      JOIN cat_tree ct ON c.parent_id = ct.id
  )
SELECT
  id,
  name,
  depth
FROM
  cat_tree

The shape

The anchor seeds the recursion with the root categories — the ones whose parent_id is missing — and the recursive member joins each child category to a row already inside the CTE on parent_id = id. One pass adds the subcategories, and a second pass finds no further matches, so the recursion stops.

Clause by clause

The anchor selects the roots and stamps them at depth 1:

SELECT id, name, parent_id, 1 AS depth
FROM categories
WHERE parent_id IS NULL

Electronics, Clothing, Home & Garden, and Books are the four root categories. Each lands in cat_tree carrying depth = 1.

The recursive member adds one level by joining children to parents already in the CTE:

UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN cat_tree ct ON c.parent_id = ct.id

The join condition c.parent_id = ct.id is the link from child to parent. The recursive pass matches Phones, Laptops, Tablets, Men's, Women's, Kids', Furniture, and Outdoor to their respective roots and stamps each with ct.depth + 1, which resolves to 2. A second pass finds no rows whose parent_id matches any id newly added at depth 2, so the recursion terminates.

The final SELECT reads the accumulated tree:

SELECT id, name, depth
FROM cat_tree

Every category from both passes is in cat_tree by now, each carrying the depth its pass stamped onto it.

The trap

The join direction in the recursive member is what controls traversal direction. c.parent_id = ct.id walks downward: each new row is a child of a row already in the CTE. Flipping the equality to c.id = ct.parent_id would walk upward instead, which is the wrong direction for a root-to-leaf traversal and would never expand past the anchor.

You practiced a two-step WITH RECURSIVE traversal — anchor on root records (where parent_id is missing), recurse one level by linking child to parent.

Return the ID, name, and depth level for every category in the hierarchy

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.