Tree Node - Problem
Given a table Tree representing a tree structure, classify each node as one of three types:
- "Leaf": A node with no children
- "Root": The top node with no parent (p_id is null)
- "Inner": A node that has both parent and children
Write a SQL query to return the id and type of each node in the tree.
Table Schema
Tree
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Unique identifier for each node |
p_id
|
int | Parent node ID, NULL for root node |
Primary Key: id
Note: Each row represents a node in the tree with its parent relationship
Input & Output
Example 1 — Complete Tree Structure
Input Table:
| id | p_id |
|---|---|
| 1 | |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
Output:
| id | type |
|---|---|
| 1 | Root |
| 2 | Inner |
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
💡 Note:
Node 1 has no parent (p_id is NULL) so it's the Root. Node 2 has parent 1 and children 4,5 so it's Inner. Nodes 3,4,5 have parents but no children so they're Leaf nodes.
Example 2 — Single Root Node
Input Table:
| id | p_id |
|---|---|
| 1 |
Output:
| id | type |
|---|---|
| 1 | Root |
💡 Note:
When there's only one node with no parent, it's classified as the Root node.
Constraints
-
1 ≤ Node count ≤ 1000 -
1 ≤ id ≤ 1000 -
Tree structure is always valid -
Each id is unique
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code