BeginnerCoder
DonateLesson 23 — Recursive hierarchy with depth
Depth and path in a hierarchy
A recursive CTE can carry extra columns from level to level. Add depth (anchor 0, then depth + 1) and a path string that grows with ||, SQLite's text-join operator: path || ' > ' || name. Sorting by path then prints a readable tree.
SELECT 'Ann' || ' > ' || 'Bo' -- 'Ann > Bo'
Why it matters: breadcrumb navigation ("Home > Books > Python") and indented org charts come straight from this pattern.
In the recursive step, make depth one more than the parent's and extend the parent's path with the new name. Expected: an indented tree from Ann down.
Run your code — when the output matches the task, you will see a green check. Then mark the lesson complete.
Test your knowledgeA quick 3-question quiz on this lesson — instant feedback.
Ready for more? Optional paid resources that pick up where this lesson leaves off.
Some links on this page are affiliate links: we may earn a commission at no extra cost to you. As an Amazon Associate I earn from qualifying purchases.
Project-based SQL Deep Dive courses if you want a structured paid path after the free lessons. Wait for a sale — prices drop often.
Browse on Udemy →Search Amazon for SQL Deep Dive books — check the publication year so the examples match current versions.
Search Amazon →