Walk a Hierarchy with a Recursive CTE

Most data worth querying has some hierarchical structure underneath it: employees report to managers who report to directors. Categories contain subcategories. A product is made of components that are made of parts. Flattening these trees in application code — loading rows and traversing them in Python loops — is slow and fragile. SQL has a better way.

A recursive CTE (Common Table Expression) is a query that references itself. It starts with a base case — the root nodes — and then repeatedly joins against the remaining rows until there's nothing left to traverse. The result is the full tree, in any depth, from a single query.

---

Step 1 — Create an org chart table. The self-referencing manager_id column is the classic hierarchy pattern:

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')

conn.executescript("""
CREATE TABLE employees (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  title       TEXT NOT NULL,
  manager_id  INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1,  'Sarah Chen',    'CEO',                 NULL),
  (2,  'Marcus Webb',   'VP Engineering',      1),
  (3,  'Priya Nair',    'VP Product',          1),
  (4,  'Tom Brandt',    'VP Sales',            1),
  (5,  'Lia Torres',    'Engineering Manager', 2),
  (6,  'Devon Park',    'Engineering Manager', 2),
  (7,  'Aisha Okonkwo', 'Product Manager',     3),
  (8,  'James Riley',   'Sales Manager',       4),
  (9,  'Sofia Reyes',   'Software Engineer',   5),
  (10, 'Kai Yamamoto',  'Software Engineer',   5),
  (11, 'Ben Osei',      'Software Engineer',   6),
  (12, 'Chloe Dupont',  'Sales Rep',           8),
  (13, 'Raj Patel',     'Sales Rep',           8);
""")
conn.commit()
print("Org chart loaded.")

---

Step 2 — Write the recursive CTE. The structure has two parts separated by UNION ALL: the anchor (root nodes) and the recursive member (each next level):

# Traverse the full org chart from the CEO down
df = pd.read_sql_query("""
  WITH RECURSIVE org_tree AS (

    -- Anchor: start at the top (no manager)
    SELECT
      id,
      name,
      title,
      manager_id,
      0 AS depth,
      name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: join each employee to their manager
    SELECT
      e.id,
      e.name,
      e.title,
      e.manager_id,
      ot.depth + 1,
      ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id

  )
  SELECT
    depth,
    SUBSTR('                ', 1, depth * 2) || name AS indented_name,
    title,
    path
  FROM org_tree
  ORDER BY path

""", conn)

print(df.to_string(index=False))

The path column accumulates each node's full ancestry as a string. The depth column tells you how deep in the tree you are. Both are built incrementally — each recursion step appends to what the previous step produced.

---

Step 3 — Query subtrees. The real power comes from filtering to a specific subtree — everyone under a given manager:

# Everyone who reports (directly or indirectly) to Marcus Webb
df = pd.read_sql_query("""
  WITH RECURSIVE reports AS (

    SELECT id, name, title, manager_id, 0 AS depth
    FROM employees
    WHERE name = 'Marcus Webb'

    UNION ALL

    SELECT e.id, e.name, e.title, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id

  )
  SELECT depth, name, title
  FROM reports
  ORDER BY depth, name

""", conn)

print(df.to_string(index=False))

conn.close()

The pattern works identically for product bill-of-materials, category trees, file systems, or any other parent-child structure. The table schema changes, but the recursive CTE structure stays exactly the same.

Most SQL interview questions about hierarchies are testing whether you know this pattern. Most candidates don't — which means knowing it is a real differentiator.

---

Want to go deeper? Browse our full resource library →