sql · level 3 · unit 4 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A recursive CTE can walk a one level at a time.
In the staff table, each person's boss_id points at their boss. That makes a tree: Grace at the top, her managers below, their teams below them. One join finds one level. Recursion finds them all.
The anchor picks the top, the row whose boss_id IS NULL. The step joins staff to the rows found last round, finding everyone whose boss is among them, and adds 1 to the .
A path built with || shows each person's chain of bosses. The recursion stops by itself when nobody is left to find.
WITH RECURSIVE team(id, name, depth, path) AS (
SELECT id, name, 0, name FROM staff WHERE boss_id IS NULL
UNION ALL
SELECT s.id, s.name, t.depth + 1, t.path || ' > ' || s.name
FROM staff AS s
JOIN team AS t ON s.boss_id = t.id
)
SELECT depth, name, path FROM team ORDER BY path;Sorting by the path keeps every team right under its boss.
guess first
Everyone below Omar, at any depth. How many?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
WITH RECURSIVE under(id, name) AS (
SELECT id, name FROM staff WHERE name = 'Omar'
UNION ALL
SELECT s.id, s.name FROM staff AS s JOIN under AS u ON s.boss_id = u.id
)
SELECT COUNT(*) - 1 AS team_size FROM under;your turn
Your turn. Climb up to the top.
Pick anyone from the staff table, such as Kit, for the anchor. Then fill the second blank with the column that points at each person's boss, so every round climbs one level up.
result
Your answer appears here as a table.
fix it
The walk down the tree is backwards. Fix it!
result
Your answer appears here as a table.
- Read the error. Does the
teamCTE have aboss_idcolumn? - Going down, you want staff whose boss was already found: their
boss_idmatches a foundid. - Change ON s.id = t.boss_id to ON s.boss_id = t.id, then press run.
nailed it
you walked the whole tree. :D
- A tree is rows that point at a parent row.
- The anchor finds the top, the step joins the next level down.
- Track depth and path to see where each row sits.
new words in your collection
- tree
- depth
This lesson isn’t saved. A free account remembers every lesson you finish.