Binary Tree Nodes SQL HackerRank Solution

In this interesting challenge we would write an SQL query to determine what type of node each of a given list of nodes is: Root Node, Inner Node or Leaf Node

🔥 A Comprehensive Binary Tree Tutorial

🔥 Data Science Tutorial for Beginners

My websites

Please join us at International Computer Programmers here:

Feel free to connect with me

Your support can help me improve my content:

SQL (Structured Query Language) is very useful for programmers, developers, data scientists and others.

[tincan]

you didnt elaborate the main correlated query part man wth, it makes me now even more frustrate now that i have no option just to sulk on this on problem that i cant wrap my head around.

[AhmadHassanUetian]

Hi,

It was a good explanation overall!

I just did not understand why did you use ALIAS? Can't we do it without using ALIAS?

I used below and got error:

SELECT n,

CASE
WHEN p IS NOT NULL THEN
CASE
WHEN (SELECT COUNT(*) FROM bst WHERE p = n ) = 0
THEN 'Leaf'
ELSE 'Inner'
END
ELSE
'Root'
END

FROM bst
ORDER BY n



Output (WRONG):

1 Leaf
2 Leaf
3 Leaf
4 Leaf
5 Leaf
6 Leaf
7 Leaf
8 Leaf
9 Leaf
10 Leaf
11 Leaf
12 Leaf
13 Leaf
14 Leaf
15 Root

[vivekjoshi]

Man I was not able to understand anything inside the CASE WHEN statement.
Could you please explain how it works when checking for INNER & LEAF?

[chrisc.]

This solution is very clean compared to the one I hacked together, lmao. Nice video.

[amgdhussein]

Can you explain why the following is wrong

SELECT b1.N,
CASE WHEN b1.P IS NULL THEN 'Root' ELSE
CASE WHEN b1.N NOT IN (SELECT b2.P FROM BST b2) THEN 'Leaf' ELSE 'Inner'
END
END
FROM BST AS b1
ORDER BY b1.N;

My thought is the second Case has same logic as yours which is making sure the N not in P

[jonathanremus]

Could you please explain why the query won't work if the table isn't alias-ed?

[slavisasavic]

I completely retyped your solution in HR (bellow) and got Wrong answer. Why is that?

select b.n,
case when b.p is not null then
case
when (select count(*) from bst where p=b.n)=0
then 'Leaf'
else 'Inner'
end
else 'Root' end
from bst b
order by b.n;

[YashvanthN]

why we are using allias ...plz explain that part

[waseem_]

Glad these comments exist, along with the fact that the audio is horrible, I'm out.