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.