SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 100 of 250

Moderate

Track tree depth

Your task

Return the names and depths of categories in the Music tree. Music has depth zero.

Carry a depth counter in the recursive CTE and increment it on each step.

Sample data

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

Expected output

namedepth
Music0
Indie1
Jazz1
Bebop2
Cool Jazz2
Show a hint

Include depth in the CTE and select t.depth + 1 in the recursive term.

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…