Tree Node: Root/Leaf/Inner
Algorithm
Problem Statement
Given a table Tree containing node ids and their parent ids (p_id), write a query to label each node as one of the following:
- Root: If the node is the root of the tree (p_id is NULL).
- Leaf: If the node is a leaf node (not a parent of any other node).
- Inner: If the node is neither a root nor a leaf.
Rules:
- Every node has a unique id.
- Output columns: id, type.
- Results must be sorted by id in ascending order.
Tests your understanding of
Tree, Conditional Logic and Subqueries
Input Tables
Tree
| id(INTEGER) | p_id(INTEGER) |
|---|---|
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
Node_Metadata
| id(INTEGER) | node_value(VARCHAR) |
|---|---|
| 1 | Start |
Tree_Audit
| audit_id(INTEGER) | id(INTEGER) | change_date(DATE) |
|---|---|---|
| 501 | 1 | 2026-02-09 |
Expected Output
| id(INTEGER) | type(VARCHAR) |
|---|---|
| 1 | Root |
| 2 | Inner |
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
Tags
MediumTreeConditional LogicSubqueries
15-20 min
62%