SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN(SELECT COUNT(*) FROM BST WHERE P = b.N) > 0 THEN 'Inner' ELSE 'Leaf' END FROM bst b ORDER BY N;`

can anyone please explain this solution of hacker rank Binary Tree Nodes ! why there are p=b.n and why it does not work when i use “from bst” and “p=bst.n” instead of “from bst b” and “p=b.n”

`

## Answer

The best way to write this code is to qualify *all* column references. So I would recommend:

SELECT b.N, (CASE WHEN b.P IS NULL THEN 'Root' WHEN (SELECT COUNT(*) FROM BST b2 WHERE b2.P = b.N) > 0 THEN 'Inner' ELSE 'Leaf' END) FROM bst b ORDER BY N;

This makes it clear that inner query is a *correlated subquery*, which is counting the number of times that a node in `BST`

has the give node but not as a parent.

What are the conditions? Logically, these are:

CASE WHEN <there is no parent> WHEN <at least one node has this node as a parent> ELSE <not a parent and no nodes have this as a parent> END

Note that I *strongly* discourage the use of `COUNT(*)`

in correlated subquery to determine if there is any match. It is much better — both from a performance perspective and a clearness perspective — to use `EXISTS`

:

SELECT b.N, (CASE WHEN b.P IS NULL THEN 'Root' WHEN EXISTS (SELECT 1 FROM BST b2 WHERE b2.P = b.N) THEN 'Inner' ELSE 'Leaf' END) FROM bst b ORDER BY N;