Recursive CTE for graph traversal in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start the recursive CTE by selecting the root node with id 1.

PostgreSQL

WITH RECURSIVE graph_path AS (
  SELECT id, parent_id FROM nodes WHERE id = [1]
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n
  JOIN graph_path gp ON n.parent_id = gp.id
)
SELECT * FROM graph_path;

Drag options to blanks, or click blank then click option'

A0

BNULL

C1

Droot

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or NULL as the starting id which may not exist.

Using a string like 'root' instead of a numeric id.

The recursive CTE starts from the root node with id 1, so the initial SELECT filters WHERE id = 1.

2fill in blank

medium

Complete the code to join the recursive CTE with the nodes table on the correct columns.

PostgreSQL

WITH RECURSIVE graph_path AS (
  SELECT id, parent_id FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n
  JOIN graph_path gp ON n.[1] = gp.id
)
SELECT * FROM graph_path;

Drag options to blanks, or click blank then click option'

Aparent_id

Bid

Cchild_id

Dnode_id

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on n.id = gp.id which causes no recursion.

Using non-existent columns like child_id or node_id.

The join condition is n.parent_id = gp.id to find children of the current node.

3fill in blank

hard

Fix the error in the recursive CTE by completing the anchor member to select id and parent_id.

PostgreSQL

WITH RECURSIVE graph_path AS (
  SELECT [1] FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n
  JOIN graph_path gp ON n.parent_id = gp.id
)
SELECT * FROM graph_path;

Drag options to blanks, or click blank then click option'

Aid, parent_id

Bid

Cparent_id

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column in the anchor member causing column mismatch.

Using * which may select extra columns.

The anchor member must select both id and parent_id to match the recursive member's columns.

4fill in blank

hard

Fill both blanks to add a depth column starting at 0 and incrementing by 1 in recursion.

PostgreSQL

WITH RECURSIVE graph_path AS (
  SELECT id, parent_id, 0 AS [1] FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, gp.[2] + 1 FROM nodes n
  JOIN graph_path gp ON n.parent_id = gp.id
)
SELECT * FROM graph_path;

Drag options to blanks, or click blank then click option'

Adepth

Blevel

Ccount

Ddistance

Attempts:

3 left

💡 Hint

Common Mistakes

Using different column names in anchor and recursive parts.

Not incrementing the depth value in recursion.

The depth column tracks how far each node is from the root. It starts at 0 and increments by 1 each recursion.

5fill in blank

hard

Fill all three blanks to filter nodes with depth less than 3 and select id, parent_id, and depth.

PostgreSQL

WITH RECURSIVE graph_path AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, gp.depth + 1 FROM nodes n
  JOIN graph_path gp ON n.parent_id = gp.id
  WHERE gp.depth [1] [2] [3]
)
SELECT id, parent_id, depth FROM graph_path;

Drag options to blanks, or click blank then click option'

A<

B3

C4

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong operators like > or =.

Using a number that is too high or too low.

The WHERE clause limits recursion to nodes with depth less than or equal to 3, so depth <= 3.

Practice

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

easy

A. To optimize query performance by indexing graph nodes

B. To create temporary tables for storing graph data

C. To repeatedly execute a query that references itself to explore connected nodes

D. To join two unrelated tables without conditions

Solution

1. Step 1: Understand the purpose of recursive CTEs

   Recursive CTEs allow a query to call itself repeatedly, which is useful for exploring connected data like graph nodes.

2. Step 2: Identify the correct use case

   Using WITH RECURSIVE lets you traverse graph structures step-by-step by referencing the CTE inside itself.

3. Final Answer:

   To repeatedly execute a query that references itself to explore connected nodes -> Option C

4. Quick Check:

   Recursive CTE = self-referencing query [OK]

Hint: Recursive CTEs repeat queries to explore connected data [OK]

Common Mistakes:

• Confusing recursive CTE with temporary tables
• Thinking it automatically optimizes performance
• Using it for unrelated table joins

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

easy

A. WITH path RECURSIVE AS (SELECT ... UNION ALL SELECT ... FROM path ...)

B. CREATE RECURSIVE TABLE path AS SELECT ...

C. SELECT RECURSIVE * FROM path

D. WITH RECURSIVE path AS (SELECT ... UNION ALL SELECT ... FROM path ...)

Solution

1. Step 1: Recall the syntax for recursive CTEs

   The correct syntax starts with WITH RECURSIVE cte_name AS ( ... ), where the CTE references itself inside.

2. Step 2: Match the syntax options

   WITH RECURSIVE path AS (SELECT ... UNION ALL SELECT ... FROM path ...) correctly uses WITH RECURSIVE path AS (SELECT ... UNION ALL SELECT ... FROM path ...). Other options misuse keywords or order.

3. Final Answer:

   WITH RECURSIVE path AS (SELECT ... UNION ALL SELECT ... FROM path ...) -> Option D

4. Quick Check:

   WITH RECURSIVE + CTE name + self-reference [OK]

Hint: Start with WITH RECURSIVE cte_name AS (...) [OK]

Common Mistakes:

• Swapping RECURSIVE and CTE name order
• Using CREATE instead of WITH
• Omitting UNION ALL between parts

3. Given the table edges(source INT, target INT) with data:
(1, 2), (2, 3), (3, 4), (2, 5)
What will the following query return?

WITH RECURSIVE path AS (SELECT source, target FROM edges WHERE source = 1 UNION ALL SELECT e.source, e.target FROM edges e JOIN path p ON e.source = p.target) SELECT * FROM path ORDER BY source, target;

medium

A. Rows: (1,2), (2,3), (2,5), (3,4)

B. Rows: (1,2), (2,3), (3,4)

C. Rows: (1,2), (2,5)

D. Empty result

Solution

1. Step 1: Identify the anchor and recursive parts

   The anchor selects edges where source=1: (1,2). The recursive part joins edges where source matches previous target.

2. Step 2: Trace recursive expansion

   From (1,2), next edges with source=2 are (2,3) and (2,5). From (2,3), next edge with source=3 is (3,4). No further edges from 5 or 4.

3. Final Answer:

   Rows: (1,2), (2,3), (2,5), (3,4) -> Option A

4. Quick Check:

   Recursive join expands all reachable edges [OK]

Hint: Follow edges from start node stepwise [OK]

Common Mistakes:

• Ignoring multiple edges from same source
• Stopping recursion too early
• Misordering results

4. Consider this recursive CTE:

WITH RECURSIVE path(node) AS (SELECT 1 UNION ALL SELECT node + 1 FROM path WHERE node < 3) SELECT * FROM path;

What is wrong if this query runs forever without stopping?

medium

A. Not selecting from the CTE in the final SELECT

B. Missing a proper termination condition in the recursive part

C. Using UNION instead of UNION ALL

D. Incorrect anchor query syntax

Solution

1. Step 1: Analyze the recursive condition

   The recursive part adds 1 to node while node < 3, but due to UNION ALL accumulating duplicates of rows satisfying the condition, recursion continues indefinitely.

2. Step 2: Identify why it loops infinitely

   If the termination condition is incorrect or not reached, recursion never stops. Here, the condition is present but ineffective against duplicates, causing infinite loop.

3. Final Answer:

   Missing a proper termination condition in the recursive part -> Option B

4. Quick Check:

   Infinite recursion = no stop condition [OK]

Hint: Check recursive WHERE clause stops recursion [OK]

Common Mistakes:

• Confusing UNION and UNION ALL effects
• Ignoring anchor query correctness
• Forgetting to select from CTE

5. You have a table employees(id INT, manager_id INT) representing a company hierarchy. How would you write a recursive CTE to find all employees under manager with id = 10, including indirect reports?

hard

A. WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.manager_id = s.id
) SELECT * FROM subordinates;

B. WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.id = s.manager_id
) SELECT * FROM subordinates;

C. WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.manager_id FROM employees e JOIN subordinates s ON e.id = s.id
) SELECT * FROM subordinates;

D. WITH RECURSIVE subordinates AS (
SELECT id FROM employees WHERE manager_id = 10
UNION ALL
SELECT e.id FROM employees e JOIN subordinates s ON e.id = s.manager_id
) SELECT * FROM subordinates;

Solution

1. Step 1: Identify the anchor query

   The anchor selects employees directly reporting to manager 10: WHERE manager_id = 10.

2. Step 2: Understand the recursive join

   The recursive part joins employees whose manager_id matches any previously found employee id, thus finding indirect reports.

3. Final Answer:

   WITH RECURSIVE subordinates AS ( SELECT id FROM employees WHERE manager_id = 10 UNION ALL SELECT e.id FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates; -> Option A

4. Quick Check:

   Join on e.manager_id = s.id to find subordinates [OK]

Hint: Join on manager_id = previous employee id to find reports [OK]

Common Mistakes:

• Swapping join conditions (id vs manager_id)
• Starting anchor with wrong WHERE clause
• Selecting wrong columns in recursive part