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.