Count descendants under each root
Your task
Return root_id, root_name, and descendant_count for every root category. Exclude the root itself from its count and sort by root ID.
Carry the root ID as you traverse so every row can be grouped under its original root.
Sample data
categories| id | name | parent_id |
|---|---|---|
| 1 | Music | NULL |
| 2 | Jazz | 1 |
| 3 | Indie | 1 |
| 4 | Bebop | 2 |
| 5 | Cool Jazz | 2 |
| 6 | Merch | NULL |
| 7 | Shirts | 6 |
Expected output
| root_id | root_name | descendant_count |
|---|---|---|
| 1 | Music | 4 |
| 6 | Merch | 1 |
Show a hint
Anchor at every root, carry root_id and root_name, and count only rows below depth zero.
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;Worked solution
WITH RECURSIVE tree(id,root_id,root_name,depth) AS (SELECT id,id,name,0 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id,t.root_id,t.root_name,t.depth+1 FROM tree t JOIN categories c ON c.parent_id=t.id) SELECT root_id,root_name,SUM(CASE WHEN depth>0 THEN 1 ELSE 0 END) AS descendant_count FROM tree GROUP BY root_id,root_name ORDER BY root_id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
