codejump
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`

idnameparent_id
1ElectronicsNULL
2Computers1
3Laptops2
4ClothingNULL
5Shoes4

Expected output (columns: id, name, root_id), ordered by id:

1|Electronics|1
2|Computers|1
3|Laptops|1
4|Clothing|4
5|Shoes|4

Hint — 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"
← All challenges