SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 102 of 250

Hard

Walk toward the root

Your task

Return Bebop and all its ancestors, one name per row.

To move upward, match the current row's parent_id to the next row's id.

Sample data

Table:categories
idnameparent_id
1MusicNULL
2Jazz1
3Indie1
4Bebop2
5Cool Jazz2
6MerchNULL
7Shirts6

Expected output

name
Bebop
Jazz
Music
Show a hint

Anchor at Bebop, then join categories on c.id = trail.parent_id.

Recursive CTEs and hierarchies guide

Start with an anchor and a stopping rule

The anchor seeds the recursive CTE. The recursive term reads previously generated rows and produces new ones. When n reaches 16, the WHERE condition stops that branch. The final SELECT controls display order.

WITH RECURSIVE powers(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n * 2 FROM powers WHERE n < 16
)
SELECT n FROM powers ORDER BY n;

Walk from a parent to its children

The categories table stores parent_id on each child. Anchor on a known category, then match the next child's parent_id to the current node's id. Carry depth alongside the ID whenever the result needs to say how far it traveled.

WITH RECURSIVE tree(id, name, depth) AS (
  SELECT id, name, 0 FROM categories WHERE name = 'Music'
  UNION ALL
  SELECT c.id, c.name, t.depth + 1
  FROM categories AS c JOIN tree AS t ON c.parent_id = t.id
)
SELECT name, depth FROM tree ORDER BY depth, name;

Handle cycles in a graph

A graph can lead back to a node you already visited. For reachability, keep only the node ID in the recursive result and use UNION so duplicate nodes are discarded. Adding a changing depth column would make repeat visits distinct rows and defeat this technique.

WITH RECURSIVE reachable(node) AS (
  VALUES (2)
  UNION
  SELECT e.target FROM edges AS e
  JOIN reachable AS r ON e.source = r.node
)
SELECT node FROM reachable ORDER BY node;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…