Recursive CTE for hierarchical data in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple recursive CTE

Given the table employees with columns id, name, and manager_id, what is the output of this query?

WITH RECURSIVE emp_cte AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e
  JOIN emp_cte c ON e.manager_id = c.id
)
SELECT id, name, level FROM emp_cte ORDER BY level, id;

PostgreSQL

WITH RECURSIVE emp_cte AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e
  JOIN emp_cte c ON e.manager_id = c.id
)
SELECT id, name, level FROM emp_cte ORDER BY level, id;

A [{"id": 1, "name": "Alice", "level": 1}, {"id": 2, "name": "Bob", "level": 2}, {"id": 3, "name": "Charlie", "level": 2}, {"id": 4, "name": "Diana", "level": 3}]

B [{"id": 1, "name": "Alice", "level": 1}, {"id": 2, "name": "Bob", "level": 1}, {"id": 3, "name": "Charlie", "level": 1}, {"id": 4, "name": "Diana", "level": 1}]

C [{"id": 1, "name": "Alice", "level": 1}, {"id": 2, "name": "Bob", "level": 3}, {"id": 3, "name": "Charlie", "level": 2}, {"id": 4, "name": "Diana", "level": 4}]

D [{"id": 4, "name": "Diana", "level": 3}, {"id": 3, "name": "Charlie", "level": 2}, {"id": 2, "name": "Bob", "level": 2}, {"id": 1, "name": "Alice", "level": 1}]

💡 Hint

Think about how the recursion builds levels starting from the top manager.

The recursive CTE starts with employees who have no manager (level 1). Then it finds employees managed by them, increasing the level by 1 each time. The output shows employees with their correct hierarchical level.

📝 Syntax

intermediate

Identify the syntax error in recursive CTE

Which option contains a syntax error in this recursive CTE query?

WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

PostgreSQL

WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

A Missing alias for recursive CTE in the second SELECT

B Incorrect use of UNION instead of UNION ALL

C No syntax error, query is valid

D Missing parentheses around the recursive part

💡 Hint

Check if the recursive CTE follows the correct syntax for PostgreSQL.

The query correctly defines a recursive CTE with a base case and a recursive part joined by UNION ALL. There is no syntax error.

❓ optimization

advanced

Optimize recursive CTE for large hierarchy

You have a large employee hierarchy and want to optimize this recursive CTE:

WITH RECURSIVE emp_path AS (
  SELECT id, name, manager_id, ARRAY[id] AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, path || e.id
  FROM employees e
  JOIN emp_path ep ON e.manager_id = ep.id
  WHERE NOT e.id = ANY(path)
)
SELECT * FROM emp_path;

Which change will improve performance by preventing unnecessary recursion?

PostgreSQL

WITH RECURSIVE emp_path AS (
  SELECT id, name, manager_id, ARRAY[id] AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, path || e.id
  FROM employees e
  JOIN emp_path ep ON e.manager_id = ep.id
  WHERE NOT e.id = ANY(path)
)
SELECT * FROM emp_path;

A Add an index on employees.manager_id column

B Replace ARRAY with JSONB to store path

C Remove the WHERE NOT e.id = ANY(path) condition

D Use UNION instead of UNION ALL

💡 Hint

Think about how the database finds rows matching manager_id in the join.

Adding an index on the manager_id column speeds up the join operation in the recursive part, reducing query time. Removing the cycle check or changing UNION ALL would cause errors or inefficiencies.

🔧 Debug

advanced

Why does this recursive CTE cause infinite recursion?

Consider this recursive CTE:

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

It causes infinite recursion. What is the most likely reason?

PostgreSQL

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

A Missing UNION ALL causes repeated rows

B There is a cycle in the categories data causing infinite loops

C The base case selects wrong rows

D Recursive CTEs cannot be used with self-joins

💡 Hint

Think about what happens if a category points to itself or creates a loop.

If the categories table has cycles (e.g., a category is its own ancestor), the recursive CTE keeps looping infinitely. To fix, you must detect and prevent cycles.

🧠 Conceptual

expert

Understanding recursive CTE output structure

You run this recursive CTE to get a hierarchy path:

WITH RECURSIVE org_path AS (
  SELECT id, name, parent_id, name::text AS path
  FROM org_units
  WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.parent_id, op.path || ' > ' || o.name
  FROM org_units o
  JOIN org_path op ON o.parent_id = op.id
)
SELECT id, path FROM org_path ORDER BY path;

What does the path column represent in the output?

PostgreSQL

WITH RECURSIVE org_path AS (
  SELECT id, name, parent_id, name::text AS path
  FROM org_units
  WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.parent_id, op.path || ' > ' || o.name
  FROM org_units o
  JOIN org_path op ON o.parent_id = op.id
)
SELECT id, path FROM org_path ORDER BY path;

A The name of the immediate parent only

B A list of all sibling org units at the same level

C The depth level of the org unit in the hierarchy

D A string showing the full hierarchy from root to the current unit separated by ' > '

💡 Hint

Look at how the path is built by concatenating names with ' > '.

The path column builds a string showing the chain of org units from the top root down to each unit, separated by ' > '. This helps visualize the full hierarchy path.

Practice

1. What is the main purpose of using a WITH RECURSIVE clause in PostgreSQL when working with hierarchical data?

easy

A. To repeatedly query related rows and build a hierarchy step-by-step

B. To create temporary tables for faster queries

C. To join two unrelated tables efficiently

D. To update multiple rows in a single statement

Solution

1. Step 1: Understand the purpose of recursive CTEs

   Recursive CTEs allow querying hierarchical data by starting with a base case and repeatedly adding related rows.

2. Step 2: Compare options with this purpose

   Only To repeatedly query related rows and build a hierarchy step-by-step describes this step-by-step building of hierarchy, while others describe unrelated SQL features.

3. Final Answer:

   To repeatedly query related rows and build a hierarchy step-by-step -> Option A

4. Quick Check:

   Recursive CTE = step-by-step hierarchy building [OK]

Hint: Recursive CTEs build hierarchies by repeating queries [OK]

Common Mistakes:

• Confusing recursive CTE with temporary tables
• Thinking recursive CTEs update data
• Assuming recursive CTEs join unrelated tables

2. Which of the following is the correct basic syntax to start a recursive CTE named tree in PostgreSQL?

easy

A. RECURSIVE WITH tree AS (SELECT ... UNION ALL SELECT ...)

B. WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...)

C. WITH tree RECURSIVE AS (SELECT ... UNION ALL SELECT ...)

D. WITH tree AS RECURSIVE (SELECT ... UNION ALL SELECT ...)

Solution

1. Step 1: Recall correct recursive CTE syntax

   The correct syntax starts with WITH RECURSIVE cte_name AS ( ... ).

2. Step 2: Match options to syntax

   Only WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...) matches this exact syntax; others misplace the keyword RECURSIVE.

3. Final Answer:

   WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...) -> Option B

4. Quick Check:

   WITH RECURSIVE before CTE name [OK]

Hint: Start with 'WITH RECURSIVE cte_name AS' [OK]

Common Mistakes:

• Placing RECURSIVE after CTE name
• Using RECURSIVE inside parentheses
• Omitting RECURSIVE keyword

3. Given the table employees(id, name, manager_id) with data:
1, 'Alice', NULL
2, 'Bob', 1
3, 'Carol', 2
What will the following query return?

WITH RECURSIVE emp_tree AS (SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN emp_tree et ON e.manager_id = et.id) SELECT name FROM emp_tree ORDER BY id;

medium

A. Alice only

B. Carol only

C. Bob, Carol only

D. Alice, Bob, Carol

Solution

1. Step 1: Understand base case and recursion

   The base case selects employee with id=1 (Alice). The recursive part adds employees whose manager_id matches ids in emp_tree.

2. Step 2: Trace recursion

   First iteration adds Bob (manager_id=1), second adds Carol (manager_id=2). So all three are included.

3. Final Answer:

   Alice, Bob, Carol -> Option D

4. Quick Check:

   Recursive CTE collects all subordinates [OK]

Hint: Recursive CTE collects all descendants starting from base [OK]

Common Mistakes:

• Ignoring base case rows
• Confusing manager_id direction
• Assuming only direct reports are included

4. Consider this recursive CTE:

WITH RECURSIVE cte AS (SELECT id, parent_id FROM items WHERE parent_id IS NULL UNION ALL SELECT i.id, i.parent_id FROM items i JOIN cte ON i.parent_id = cte.id) SELECT * FROM cte;

It returns no rows. What is the most likely cause?

medium

A. The base case SELECT returns no rows because no parent_id is NULL

B. The JOIN condition is reversed and should be cte.parent_id = i.id

C. The recursive part uses UNION instead of UNION ALL

D. The CTE is missing an ORDER BY clause

Solution

1. Step 1: Check base case condition

   The base case selects rows where parent_id IS NULL. If none exist, base returns zero rows.

2. Step 2: Understand recursion depends on base

   Without base rows, recursion never starts, so no rows returned.

3. Final Answer:

   The base case SELECT returns no rows because no parent_id is NULL -> Option A

4. Quick Check:

   Base case must return rows to start recursion [OK]

Hint: Check base case returns rows before recursion [OK]

Common Mistakes:

• Assuming JOIN condition is wrong without checking data
• Thinking UNION vs UNION ALL causes no rows
• Believing ORDER BY affects recursion output

5. You have a table folders(id, name, parent_id) representing nested folders. Write a recursive CTE query to list all folders with their full path from the root, separated by '/'. Which query correctly achieves this?

hard

A. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NOT NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

B. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, '/' || name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, '/' || fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

C. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

D. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path + '/' + f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

Solution

1. Step 1: Identify correct base case and path initialization

   The base case must select root folders (parent_id IS NULL) and set path to just the folder name without leading '/'. The recursive part builds path as parent_path || '/' || name.

2. Step 2: Compare options with correct logic

   The correct query uses parent_id IS NULL for base, name AS path, and fp.path || '/' || f.name for recursion (PostgreSQL string concat). Distractors fail by using invalid + concat, adding extra '/' (causing '//'), or starting from non-roots (parent_id IS NOT NULL).

3. Final Answer:

   WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path; -> Option C

4. Quick Check:

   Use base root rows + '||' concat for paths [OK]

Hint: Use '||' for string concat and start from root folders [OK]

Common Mistakes:

• Using '+' instead of '||' for string concatenation
• Starting recursion from non-root folders
• Adding extra '/' at path start