Recursive CTE for hierarchical data in SQL - 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).

SQL

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

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

ANULL

BNOT NULL

C0

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'IS' to check for NULL

Selecting rows where parent_id is 0 instead of NULL

The root nodes in a hierarchical table usually have parent_id as NULL. So the base case selects rows where parent_id IS NULL.

2fill in blank

medium

Complete the recursive part to join the CTE with the employees table to find subordinates.

SQL

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
)

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

Aname

Bid

Cparent_id

Dmanager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on employee id instead of parent_id

Using wrong column names like 'manager_id' if not defined

The recursive step joins employees where their parent_id matches the id of the current subordinates, thus finding direct reports.

3fill in blank

hard

Fix the error in the final SELECT statement to get all subordinates' names.

SQL

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.parent_id = s.id
)
SELECT [1] FROM subordinates;

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

Aname

Bid, parent_id

C*

Dparent_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only ids or parent_ids instead of names

Selecting * which is allowed but not focused on the question

The question asks for all subordinates' names, so selecting the name column is correct.

4fill in blank

hard

Fill both blanks to add a level column showing depth in the hierarchy and increment it in recursion.

SQL

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

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names like 'level' and 'depth' inconsistently

Forgetting to increment the level in the recursive part

The column name must be consistent in both parts of the CTE. Here, level is used to track depth, so both blanks should be level.

5fill in blank

hard

Fill all three blanks to filter subordinates at level greater than 2 and order by level ascending.

SQL

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 name, level FROM subordinates
WHERE level [1] 2
ORDER BY level [2];

-- Use [3] to specify ascending order

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

A>

B<

CASC

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' to filter levels

Ordering by DESC when ascending is requested

To get subordinates deeper than level 2, use level > 2. To order ascending, use ORDER BY level ASC. The third blank clarifies the order direction as ASC.