Classify Tree Nodes with SQL
Reported by candidates from Oracle's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Oracle put a tree-classification SQL question in front of candidates in September 2026, and the 100,000 row cap is the detail that matters. You can't loop row by row or self-join your way into a slow mess. You need one clean pass. The table is tree_nodes with id and pid, and every node gets labeled Root, Inner, or Leaf. It looks like a tree problem, but it's really a NULL handling and existence check problem. If the query clicks, it's five minutes. If you blank on the NOT IN trap, StealthCoder is the safety net running invisibly during the live OA.
The problem
The table tree_nodes stores one rooted tree. Each row contains a unique node identifier id and its parent identifier pid. The root has pid = NULL; every other pid names an existing node. Return every node with exactly one of these labels: Root: the node has no parent. Inner: the node has a parent and at least one child. Leaf: the node has a parent and no children. Output the columns id and node_type, ordered by id in ascending order. Tables tree_nodes: id PK (Integer), pid (Integer) Constraints 1 <= tree_nodes row count <= 100000. id is a unique positive integer. Exactly one row has pid = NULL. Every non-null pid names another row's id. The parent relationships form one connected acyclic tree.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is that you don't need recursion. Depth never matters, only two facts per node: does it have a parent, and does it appear as someone else's pid. Use a CASE expression. If pid IS NULL, it's Root. Else if id appears in the set of pids, it's Inner. Otherwise Leaf. Check order matters, Root first. For the child check, use EXISTS or a LEFT JOIN on the same table (t.id = c.pid) and test c.id IS NULL. The classic pitfall is NOT IN (SELECT pid...). The root's pid is NULL, so NOT IN returns no rows and every Leaf vanishes. Filter out NULLs or use EXISTS. Finish with ORDER BY id. At 100,000 rows, a single join or semi-join is fine. If you freeze mid-assessment, StealthCoder can hand you the CASE skeleton as a hedge.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Classify Tree Nodes with SQL cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
This OA pattern shows up on LeetCode as tree node. If you have time before the OA, drill that.
You've seen the question.
Make sure you actually pass Oracle's OA.
Oracle reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Classify Tree Nodes with SQL FAQ
How hard is the Oracle Classify Tree Nodes SQL question really?+
Easy to medium. No recursive CTE is needed. It's a CASE expression plus a check for whether a node appears as a parent. The difficulty is NULL handling, not logic. If you know the NOT IN trap, you're mostly done.
What's the trick to this problem?+
Only two facts matter: is pid NULL, and does the id show up in anyone's pid column. Order the CASE branches Root, Inner, Leaf. Use EXISTS or a LEFT JOIN to detect children instead of NOT IN, which breaks on the NULL root pid.
Do I need a recursive CTE?+
No. Labels depend only on a node's direct parent and direct children, not depth or ancestry. A recursive CTE would add complexity and risk for nothing. A self join or a correlated EXISTS handles it in a single pass.
Will 100,000 rows cause performance trouble?+
Not with a join or EXISTS on the primary key id. Avoid correlated subqueries that scan unindexed columns repeatedly if you can. A LEFT JOIN of tree_nodes to itself on id = pid, grouped or deduplicated carefully, scales fine at this size.
How do I prepare for this in 48 hours?+
Write the query three ways: CASE with EXISTS, CASE with LEFT JOIN, and CASE with IN on a NULL-filtered subquery. Test each on a one-node tree, since a lone root should return Root. Then practice the same pattern on other parent-child tables.