Classify Category Nodes

Problem

A product catalog stores its category hierarchy as a parent-pointer table.

```
Category
+-----------+------+
| Column | Type |
+-----------+------+
| node_id | int |
| parent_id | int |
+-----------+------+
node_id is the primary key. parent_id points to this node's parent node_id, or is NULL for the top of the tree.
```

Classify every node into one role:

- **Top** - the node has no parent (`parent_id` is NULL).
- **Branch** - the node has a parent and is itself the parent of at least one other node.
- **Leaf** - the node has a parent and no children.

Return `node_id` and its `role`, in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 5 rows with the columns node_id, role.

Starter code (SQL)

SELECT *
FROM Category;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium