How should I practice Classify Tree Nodes with SQL?

Start by reading the full statement, solve the visible examples, then compare the pattern against related Oracle phone screen problems in FastPrep.

Is Classify Tree Nodes with SQL associated with Oracle?

FastPrep tags this as a Oracle phone screen coding practice problem when the problem catalog has that company and stage context.

Problem · Database

Classify Tree Nodes with SQL

● Easy

OracleFULLTIMEPHONE SCREEN

Problem statement

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.

Table schema

MySQLPostgreSQL

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

tree_nodes

Column	Type	Nullable	Description
idPK	Integer	No	—
pid	Integer	Yes	—

Expected result

Your query or function must return these columns.

Column	Type	Nullable	Description
id	Integer	No	—
node_type	Text	No	—

Row order: must match exactly. Numeric tolerance: 0.

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.