[SOLVED] Can anyone please explain this solution of hacker rank Binary Tree Nodes?

Can anyone please explain this solution of hacker rank Binary Tree Nodes?

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?

Solution

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;