Classify IDs by Leaf, Root, or Inner | Dataford Interview Questions

Given a table of ids and parent ids, write SQL to determine each id's type (Leaf, Root, or Inner). Asked in the phone screen stage. A root has no parent, an inner node has a parent and children, and a leaf has only a parent.

Output

Return one row for every row in tree.
Include id and type, where type is Root, Inner, or Leaf.
Order results by id ascending.

Schema

tree

Column	Type	Description
idPK	INT	Unique identifier for the node
parent_id	INT	Identifier of the node's parent, or NULL for a root

Your question is Classify IDs by Leaf, Root, or Inner. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Output

Return one row for every row in tree.
Include id and type, where type is Root, Inner, or Leaf.
Order results by id ascending.

Schema

tree

Column	Type	Description
idPK	INT	Unique identifier for the node
parent_id	INT	Identifier of the node's parent, or NULL for a root

Your question is Classify IDs by Leaf, Root, or Inner. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.