Tree Node Types

Problem

Schema: Tree(id INT, parent_id INT). The root has parent_id IS NULL. Label each node as Root / Inner / Leaf and return (id, type).

Constraints

• Tree may be empty.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

SELECT t.id,
       CASE
         WHEN t.parent_id IS NULL THEN 'Root'
         WHEN t.id NOT IN (SELECT parent_id FROM Tree WHERE parent_id IS NOT NULL) THEN 'Leaf'
         ELSE 'Inner'
       END AS type
FROM Tree t
ORDER BY t.id;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.