Roll album stock up to root categories
Your task
Return root_id, root_name, and total_stock for each root. Match album genre to category name anywhere in that root’s tree, including the root. Use zero if no album matches and sort by root ID.
A hierarchy can connect an album’s genre to a broader department.
Sample data
| 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 | total_stock |
|---|---|---|
| 1 | Music | 22 |
| 6 | Merch | 0 |
Show a hint
Carry root information in a recursive CTE, then left join each category name to albums.genre before summing stock.
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,name,root_id,root_name) AS (SELECT id,name,id,name FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id,c.name,t.root_id,t.root_name FROM tree t JOIN categories c ON c.parent_id=t.id) SELECT t.root_id,t.root_name,COALESCE(SUM(a.stock),0) AS total_stock FROM tree t LEFT JOIN albums a ON a.genre=t.name GROUP BY t.root_id,t.root_name ORDER BY t.root_id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
