Category Tree - Full Ancestor Path
Preview mode. Log in to edit, run, submit, and save progress.
Medium
Category Tree - Full Ancestor Path
You have a Categories table representing a tree (each category has an optional parent). Write a SQL query to produce the full path from the root to each node, formatted as 'Root > Child > Grandchild', and the depth of each node (root = 0). Return (id, name, full_path, depth) ordered by id. Table: Categories
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| name | VARCHAR | Category name |
| parent_id | INT | NULL if this is a root category |
Examples
Example 1
Anchor the CTE at root nodes (parent_id IS NULL). Recursively append each child's name to the parent's full_path and increment depth.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
query.sql