Binary Tree Nodes - Problem

Given a table Tree representing a binary tree structure, write a SQL query to classify each node as one of the following types:

  • Root: The node that has no parent (P is NULL)
  • Leaf: The node that has no children (not referenced as a parent by any other node)
  • Inner: The node that has both a parent and at least one child

Return the result table with columns N (node value) and Type (node classification), ordered by node value in ascending order.

Table Schema

Tree
Column Name Type Description
N PK int Node value (unique identifier)
P int Parent node value (NULL for root node)
Primary Key: N
Note: Each row represents a node in the binary tree with its parent reference

Input & Output

Example 1 — Simple Binary Tree
Input Table:
N P
1 2
2
3 2
Output:
N Type
1 Leaf
2 Root
3 Leaf
💡 Note:

Node 2 is the root (P is NULL). Nodes 1 and 3 are children of node 2 with no children themselves, making them leaf nodes.
Example 2 — Tree with Inner Node
Input Table:
N P
1 2
2 5
3 2
4 5
5
Output:
N Type
1 Leaf
2 Inner
3 Leaf
4 Leaf
5 Root
💡 Note:

Node 5 is the root. Node 2 is an inner node (has parent 5 and children 1,3). Node 5 is also inner (root with children). Nodes 1, 3, and 4 are leaves.
Example 3 — Single Node Tree
Input Table:
N P
1
Output:
N Type
1 Root
💡 Note:

Single node with no parent becomes the root node, even though it has no children.

Constraints

  • 1 ≤ N ≤ 1000
  • P is NULL for root nodes
  • Each node value N is unique
  • Valid binary tree structure guaranteed

Visualization

Tap to expand
Binary Tree Nodes Classification INPUT N P 1 2 2 5 3 2 4 3 5 NULL 6 5 7 6 Tree Structure: 5 2 6 1 3 7 4 ALGORITHM STEPS 1 Check Parent (P) If P is NULL --> Root 2 Find Children Check if N appears in P column 3 Classify Node Has children? Inner : Leaf 4 Order by N Sort results ascending SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM Tree) THEN 'Inner' ELSE 'Leaf' END AS Type FINAL RESULT N Type 1 Leaf 2 Inner 3 Inner 4 Leaf 5 Root 6 Inner 7 Leaf Classification Legend: Root P=NULL Inner Has children Leaf No children OK - 7 nodes classified Key Insight: A node's classification depends on TWO conditions: (1) whether it has a parent (P column value), and (2) whether it appears as a parent of other nodes. Use a subquery to check if N exists in the P column. This self-referential check with CASE WHEN elegantly handles all three node types in a single query. TutorialsPoint - Binary Tree Nodes | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Facebook 8
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen