MediumSQL
Category Tree Root Finder
SQLDatabasesRecursive CTE
An e-commerce catalog stores categories in a self-referencing tree.
Write a query that returns every category alongside the **id of its
top-level root ancestor** (a root category is its own root).
Table: categories
| id | name | parent_id |
|---|---|---|
| 1 | Electronics | NULL |
| 2 | Computers | 1 |
| 3 | Laptops | 2 |
| 4 | Clothing | NULL |
| 5 | Shoes | 4 |
Expected output (columns: id, name, root_id), ordered by id:
1|Electronics|1
2|Computers|1
3|Laptops|1
4|Clothing|4
5|Shoes|4Hint — this is the mirror image of the ancestry-path challenge: instead
of accumulating a growing path, carry the root's own id unchanged all
the way down through every recursive step.
Sample tests
Test #1Two separate trees, three levels deep
Input: "CREATE TABLE categories (id INTEGER, name TEXT, parent_id INTEGER);\nINSERT INTO categories VALUES\n (1, 'Electronics', NULL),\n (2, 'Computers', 1),\n (3, 'Laptops', 2),\n (4, 'Clothing', NULL),\n (5, 'Shoes', 4);"
Output: "1|Electronics|1\n2|Computers|1\n3|Laptops|1\n4|Clothing|4\n5|Shoes|4"
Test #2Single root category is its own root
Input: "CREATE TABLE categories (id INTEGER, name TEXT, parent_id INTEGER);\nINSERT INTO categories VALUES\n (1, 'Top', NULL);"
Output: "1|Top|1"