SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 114 of 250

Hard

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

Choose a table
idnameparent_id
1MusicNULL
2Jazz1
3Indie1
4Bebop2
5Cool Jazz2
6MerchNULL
7Shirts6

Expected output

root_idroot_nametotal_stock
1Music22
6Merch0
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;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…