Category Tree - Full Ancestor Path

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

Column Name	Example Value
id	1
name	Electronics
parent_id	NULL

Example

Categories

id	name	parent_id
1	Electronics	NULL
2	Computers	1
3	Laptops	2
4	Gaming Laptops	3
5	Phones	1
6	Smartphones	5

Output

id	name	full_path	depth
1	Electronics	Electronics	0
2	Computers	Electronics > Computers	1
3	Laptops	Electronics > Computers > Laptops	2
4	Gaming Laptops	Electronics > Computers > Laptops > Gaming Laptops	3
5	Phones	Electronics > Phones	1
6	Smartphones	Electronics > Phones > Smartphones	2

Explanation:

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.