A recursive CTE can expand not only from leaf to root (bottom-up), the reverse of root-to-leaf (top-down). Retrieving the current location and its ancestor categories as an e-commerce breadcrumb is a typical use.

WITH RECURSIVE ancestors AS (
  -- ① Anchor member: start from the target node (leaf)
  SELECT category_id, category_name, parent_id, 0 AS level
  FROM  categories
  WHERE category_id = :target_id  -- specify the starting leaf node

  UNION ALL

  -- ② Recursive member: follow the current parent_id to retrieve the parent (upward)
  SELECT c.category_id, c.category_name, c.parent_id, a.level + 1
  FROM  categories c
  JOIN  ancestors a ON c.category_id = a.parent_id  -- ← the JOIN direction is reversed
)
SELECT * FROM ancestors ORDER BY level DESC;

An e-commerce site has a category tree. Retrieve all ancestors of category_id=6 (Men's Sneakers) and return category_id, category_name, parent_id, level with the root at the top. Sort by level DESC so the root comes first.

Expected output (level DESC = root first):

level=0 is the target node itself; larger levels are higher in the hierarchy, toward the root. level DESC puts the root first.

WITH RECURSIVE ancestors AS (

  -- ① Anchor member: start from the target node (Men's Sneakers)
  SELECT
    category_id,
    category_name,
    parent_id,
    0 AS level         -- starting node has level = 0
  FROM  categories
  WHERE category_id = 6  -- specify Men's Sneakers as the starting point

  UNION ALL

  -- ② Recursive member: use the current parent_id to retrieve the row whose category_id is the parent
  SELECT
    c.category_id,
    c.category_name,
    c.parent_id,
    a.level + 1          -- move upward one level at a time
  FROM  categories c
  JOIN  ancestors a ON c.category_id = a.parent_id  -- ← reverse JOIN direction for bottom-up traversal

)
SELECT
  category_id,
  category_name,
  parent_id,
  level
FROM  ancestors
ORDER BY level DESC;  -- sort by descending level so the root comes first

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. Recursion iteration 3
  5. Recursion iteration 4
  6. Outer query
  */

Comments on social networks and forums have a nested thread structure of replies to comments and replies to replies. A recursive CTE can expand the whole thread, but invalid cycles or abnormal depth can cause an infinite loop. Storing depth in a column and setting an upper bound in WHERE is a standard production guard.

WITH RECURSIVE thread AS (
  SELECT comment_id, content, parent_id, 0 AS depth
  FROM  comments WHERE parent_id IS NULL

  UNION ALL

  SELECT c.comment_id, c.content, c.parent_id, t.depth + 1
  FROM  comments c
  JOIN  thread t ON c.parent_id = t.comment_id
  WHERE t.depth + 1 < 3   -- ← recurse only from rows where depth is less than 3
)

The comments table stores comments on an article and their replies. Expand every comment into a thread, but do not retrieve depth 3 or greater (depth >= 3). Return comment_id, content, parent_id, depth, indent, where indent is an indentation string of depth × 2 spaces. Sort by comment_id ascending.

Expected output (comment_id ascending):

comment_id=7 (depth=3) is not retrieved. Only 6 rows are returned.

WITH RECURSIVE thread AS (

  -- ① Anchor member: retrieve root comments (not replies)
  SELECT
    comment_id,
    content,
    parent_id,
    0                AS depth,
    ''               AS indent  -- root indentation is an empty string
  FROM  comments
  WHERE parent_id IS NULL

  UNION ALL

  -- ② Recursive member: retrieve child comments, but recurse only from rows where depth < 3
  SELECT
    c.comment_id,
    c.content,
    c.parent_id,
    t.depth + 1,
    REPEAT('  ', t.depth + 1)  -- generate two spaces for each depth level
  FROM  comments c
  JOIN  thread t ON c.parent_id = t.comment_id
  WHERE t.depth + 1 < 3          -- block recursion to depth 3 and beyond (this is the guard)

)
SELECT
  comment_id,
  content,
  parent_id,
  depth,
  indent
FROM  thread
ORDER BY comment_id;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. Recursion iteration 3
  5. Outer query
  */

File-system folders form a hierarchy, and each folder's size is the sum of files directly inside it and files in every descendant folder. This can be implemented by expanding every path with a recursive CTE and aggregating with GROUP BY.

The idea is that each file contributes not only to its own folder but also to every ancestor folder, so first build a (file, ancestor folder) mapping by bottom-up recursion and then sum it with GROUP BY.

-- Step 1: expand (folder_id, ancestor_folder_id) for each file
WITH RECURSIVE folder_ancestors AS ( ... )
-- Step 2: sum file sizes by ancestor_folder_id
SELECT ancestor_folder_id, SUM(file_size) AS total_size
FROM  folder_ancestors
GROUP BY ancestor_folder_id;

There are two tables for managing folders and files. Find each folder's total size (the sum of all file sizes below it). Return folder_id, folder_name, total_size_kb, sorted by folder_id ascending.

Expected output (folder_id ascending):

Root = Documents(710) + Images(3500) = 4210. Documents = Work(510) + Personal(200) = 710.

WITH RECURSIVE folder_tree AS (

  -- ① Anchor member: register each folder itself as its own ancestor
  SELECT
    folder_id AS descendant_id,  -- folder ID on the descendant side
    folder_id AS ancestor_id     -- include the folder itself as an ancestor
  FROM  folders

  UNION ALL

  -- ② Recursive member: generate (descendant, ancestor) pairs from child folders to parent folders
  SELECT
    ft.descendant_id,              -- keep the original descendant folder ID
    f.parent_id AS ancestor_id    -- the parent folder becomes the new ancestor
  FROM  folder_tree ft
  JOIN  folders f ON f.folder_id = ft.ancestor_id
  WHERE f.parent_id IS NOT NULL   -- stop after reaching the root

)
SELECT
  fo.folder_id,
  fo.folder_name,
  SUM(fi.size_kb) AS total_size_kb  -- sum all file sizes mapped to the ancestor folder
FROM  folder_tree ft
JOIN  files fi ON fi.folder_id = ft.descendant_id  -- files belong on the descendant side
JOIN  folders fo ON fo.folder_id = ft.ancestor_id  -- retrieve the folder name from the ancestor side
GROUP BY fo.folder_id, fo.folder_name
ORDER BY fo.folder_id;

/*
  Execution order (SQL logical evaluation order):
  1. Anchor member
  2. Recursion iteration 1
  3. Recursion iteration 2
  4. Recursion iteration 3: all NULL
  5. Outer query
  */

In an organization chart where reports form chains beneath managers, distinguish direct reports from all direct and indirect reports (span of control). Expand all descendants with WITH RECURSIVE and then aggregate with COUNT to solve this pattern.

WITH RECURSIVE subordinates AS (
  -- Anchor: start from each employee and expand rows whose manager_id matches
  SELECT emp_id, manager_id, 0 AS depth, emp_id AS root_manager_id
  FROM  employees
  UNION ALL
  SELECT e.emp_id, e.manager_id, s.depth + 1, s.root_manager_id
  FROM  employees e JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT root_manager_id, COUNT(*) - 1 AS total_subordinates  -- -1 excludes the employee itself
FROM  subordinates
GROUP BY root_manager_id;

The employees table stores employee-manager relationships. Find each manager's direct report count (direct_reports) and total report count (total_subordinates, including direct and indirect reports). Return emp_id, emp_name, direct_reports, total_subordinates only for employees with at least one direct report, sorted by emp_id ascending.

Expected output (emp_id ascending; only employees with at least one direct report):

Dave, Eve, and Frank are excluded because they have no reports.

WITH RECURSIVE sub_tree AS (

  -- ① Anchor member: register all employees as roots of their own trees
  SELECT
    emp_id,
    manager_id,
    emp_id AS root_id,  -- label showing which manager tree this row belongs to
    0      AS depth
  FROM  employees

  UNION ALL

  -- ② Recursive member: add employees whose manager_id is the previous working row's emp_id
  SELECT
    e.emp_id,
    e.manager_id,
    s.root_id,           -- propagate root_id (keep the tree owner unchanged)
    s.depth + 1
  FROM  employees e
  JOIN  sub_tree s ON e.manager_id = s.emp_id

),
direct AS (                                -- ③ Direct report count: count directly by manager_id
  SELECT
    manager_id,
    COUNT(*) AS direct_cnt
  FROM  employees
  WHERE manager_id IS NOT NULL
  GROUP BY manager_id
),
total AS (                                 -- ④ Total report count: count sub_tree rows with depth > 0 (excluding the employee itself)
  SELECT
    root_id,
    COUNT(*) AS total_cnt
  FROM  sub_tree
  WHERE depth > 0                          -- exclude depth=0 because it is the employee itself
  GROUP BY root_id
)
SELECT
  e.emp_id,
  e.emp_name,
  d.direct_cnt  AS direct_reports,
  t.total_cnt   AS total_subordinates
FROM  employees e
JOIN  direct d ON d.manager_id = e.emp_id  -- employees with direct reports are managers
JOIN  total  t ON t.root_id   = e.emp_id
ORDER BY e.emp_id;

/*
  Execution order (SQL logical evaluation order):
  1. sub_tree CTE (recursion)
  2. direct CTE
  3. total CTE
  4. Outer query
  */