Recursive CTE for hierarchical data in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start the recursive CTE by selecting the root nodes (where parent_id is NULL).

PostgreSQL

WITH RECURSIVE subordinates AS ( SELECT id, name, parent_id FROM employees WHERE parent_id IS [1] ) SELECT * FROM subordinates;

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

ANOT NULL

B0

C''

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using = NULL instead of IS NULL

Using 0 or empty string instead of NULL

The root nodes in a hierarchical table usually have parent_id IS NULL. This selects the top-level employees.

2fill in blank

medium

Complete the recursive part to join employees to their subordinates by matching parent_id to id.

PostgreSQL

WITH RECURSIVE subordinates AS ( SELECT id, name, parent_id FROM employees WHERE parent_id IS NULL UNION ALL SELECT e.id, e.name, e.parent_id FROM employees e JOIN subordinates s ON e.[1] = s.id ) SELECT * FROM subordinates;

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

Aname

Bmanager_id

Cparent_id

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on id = id instead of parent_id = id

Using wrong column names like manager_id if not present

We join employees where their parent_id matches the id of the current subordinates to find the next level.

3fill in blank

hard

Fix the error in the recursive CTE by correctly aliasing the recursive table in the join.

PostgreSQL

WITH RECURSIVE subordinates AS ( SELECT id, name, parent_id FROM employees WHERE parent_id IS NULL UNION ALL SELECT e.id, e.name, e.parent_id FROM employees e JOIN [1] ON e.parent_id = subordinates.id ) SELECT * FROM subordinates;

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

Aemployees

Bsubordinates

Ce

Dmanagers

Attempts:

3 left

💡 Hint

Common Mistakes

Joining employees to employees instead of to subordinates

Using wrong alias names in the join

The recursive CTE must join to its own alias, which is subordinates here, not the base table.

4fill in blank

hard

Fill both blanks to add a level column that increments with each recursion and start it at 1.

PostgreSQL

WITH RECURSIVE subordinates AS ( SELECT id, name, parent_id, 1 AS [1] FROM employees WHERE parent_id IS NULL UNION ALL SELECT e.id, e.name, e.parent_id, s.[2] + 1 FROM employees e JOIN subordinates s ON e.parent_id = s.id ) SELECT * FROM subordinates;

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

Alevel

Bdepth

Ccount

Drank

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for the level column in anchor and recursive parts

Starting level at 0 instead of 1

We add a level column starting at 1 for root nodes and increment it by 1 for each recursion to track depth.

5fill in blank

hard

Fill all three blanks to select id, name, and level from the recursive CTE and order by level ascending.

PostgreSQL

WITH RECURSIVE subordinates AS ( SELECT id, name, parent_id, 1 AS level FROM employees WHERE parent_id IS NULL UNION ALL SELECT e.id, e.name, e.parent_id, s.level + 1 FROM employees e JOIN subordinates s ON e.parent_id = s.id ) SELECT [1], [2], [3] FROM subordinates ORDER BY level ASC;

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

Aid

Bname

Clevel

Dparent_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting parent_id instead of level

Ordering by id instead of level

The final SELECT chooses id, name, and level columns and orders results by level ascending to show hierarchy levels.