Each node in the tree can be one of three types: "Leaf": if the node is a leaf node. "Root": if the node is the root of the tree. "Inner": If the node is neither a leaf node nor a root node.
This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.
Each node in the tree can be one of three types: "Leaf": if the node is a leaf node. "Root": if the node is the root of the tree. "Inner": If the node is neither a leaf node nor a root node.
Hierarchical data appears everywhere: org charts, menus, categories, and dependency trees. This problem is a simple introduction to reasoning about parent-child structure inside one table, which is the foundation for more advanced recursive SQL later.
idp_idSELECT
id,
CASE
WHEN p_id IS NULL THEN 'Root'
WHEN id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Inner'
ELSE 'Leaf'
END AS type
FROM treeThe logic mirrors the node definitions directly. A root has no parent, an inner node appears as someone else’s parent, and everything left over is a leaf.
SELECT
t1.id,
CASE
WHEN t1.p_id IS NULL THEN 'Root'
WHEN t2.id IS NOT NULL THEN 'Inner'
ELSE 'Leaf'
END AS type
FROM tree t1
LEFT JOIN tree t2
ON t1.id = t2.p_id
GROUP BY t1.id, t1.p_idThe join version makes child existence explicit by linking each node to any rows that name it as a parent. It is often easier to extend when the next question asks for child counts or subtree metrics.
Do not identify leaves solely by checking p_id IS NOT NULL. A non-root node can still be an inner node if other rows reference it as their parent. You must check both parent status and child existence.
Because the prompt defines root as its own category. The classification is mutually exclusive: root first, then inner, then leaf.
It teaches you to reason about self-referential tables, where the same relation plays both the parent role and the child role at the same time.
Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.
Unlock the power of WITH RECURSIVE. Learn how to traverse hierarchies, generate continuous dates, and solve graph problems directly in SQL.
Learn how to add conditional logic to your SQL queries using CASE statements. Transform data and categorize results with ease.
Learn the two most important database relationships. Design one-to-many and many-to-many tables with real SQL examples, diagrams, and interactive queries.
If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.
Advanced SQL
Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.
Schema
Useful when the hard part is not the query itself but the table design, relationships, and semi-structured data underneath it.
If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.
Swap every pair of adjacent seat IDs while leaving the final row untouched if the seat count is odd.
Calculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.
Return only the customers whose distinct purchased product set covers every product listed in the catalog.