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
Link to Challenge - www.hackerrank.com/challenges...
👉 Learn practical SQL here - • Practical SQL for Begi...
👉 SQL Tutorials here www.kindsonthegenius.com/mssql
👉 How to Setup MS SQL - • How to Create MS SQL D...
🔥 A Comprehensive Binary Tree Tutorial
1. Defining a Tree - • Part 1 - Defining a Bi...
2. Define the Recursive Insert function • Part 2 - Define the Re...
3. Adding Nodes and Building the Tree • Part 3 - Insert Nodes ...
4. Printing all nodes InOrder • Part 4 - InOrder Trave...
5. Printing all Nodes PreOrder • Part 5 - PreOrder Trav...
6. Printing all Nodes PostOrder • Part 6 PostOrder Tra...
7. Breadth-First Search (BFS) - Algorithm Using Queues • Part 7 - Breadth First...
🔥 Data Science Tutorial for Beginners
* www.kindsonthegenius.com/cate...
* www.datarmatics.com/data-scie...
www.munonye.com
www.datarmatics.com
Solution to 30 Days HackerRank Coding Challenge here - bit.ly/38cWPyJ
My Free Python Programming Tutorial - www.kindsonthegenius.com/python
My websites
All Tutorials - www.kindsonthegenius.com
SpringBoot/Angular - www.munonye.com
Data Science Stuff - www.datarmatics.com
Please join us at International Computer Programmers here:
👉 / 1447709731988533
Feel free to connect with me
❤️ Kindson on Instagram: / kindsonthegius
❤️ Kindson on LinkedIn: / kindson
❤️ Kindson on Twitter: / kindsonm
❤️ Kindson on Facebook: / kindsonm
Your support can help me improve my content:
✅ Buy me a coffee: www.buymeacoffee.com/PWsXo7h13
SQL (Structured Query Language) is very useful for programmers, developers, data scientists and others.

[@anishdeshpande395]

Very well explained. Thank you

[@vivekjoshi3769]

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?

[@ChesterLaw-oh2hp]

If I understand it correctly, for the first CASE WHEN statement, it provides a constrain to only run the second CASE WHEN statement when b.P is NOT NULL. As for the second CASE WHEN statement, the WHERE constrain ask the program to only count +1 whenever each b.N is equal to any row in column P.
It is important to know that he does two things here to make his query works:
First, he ask the program to pull the BST table again, by using CASE WHEN SELECT (COUNT(*) FROM BST (

[@tincan5358]

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.

[@matthewstaton7192]

I concur. I cannot wrap my head around this. I found a solution that I understand, but I cannot wrap my head around why the solution works and how it can work on other trees....

[@chrisc.4144]

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

[@jonathanremus5697]

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

[@KindsonTheTechPro]

We are using a subquery on a second table which is the same as the first one. Since the two tables are the same, one of them must be aliased.

[@user-br5sw7de5g]

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

[@KindsonTheTechPro]

We are using a subquery on a second table which is the same as the first one. Since the two tables are the same, one of them must be aliased.

[@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

[@slavisasavic3881]

Did you managed to find correct solution on this problem?

[@KindsonTheTechPro]

We are using a subquery on a second table which is the same as the first one. Since the two tables are the same, one of them must be aliased.

[@amgdhussein7040]

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

[@ayushshaji6697]

The modification includes adding the condition b2.P IS NOT NULL within the subquery to exclude NULL values from the subquery results. This will ensure that only valid parent values are considered for identifying leaf and inner nodes .
SELECT b1.N,
CASE
WHEN b1.P IS NULL THEN 'Root'
WHEN b1.N NOT IN (SELECT b2.P FROM BST b2 WHERE b2.P IS NOT NULL) THEN 'Leaf'
ELSE 'Inner'
END
FROM BST AS b1
ORDER BY b1.N;
(you can use 2 case statements too)

[@ayushshaji6697]

'IN', while matching with NULL values behaves not as expected. In most cases when compared with a NULL values using 'IN' the answer will be 'unknown' rather than 'true' or 'false'.

[@slavisasavic3881]

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;

[@ayushshaji6697]

I executed your query and got the right answer.

[@KindsonTheTechPro]

I think you'll need to choose the right Database. In this case it's MySQL

[@waseem_498]

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

[@thepassingpawn]

thats rude. you dont have any manners my friend. you clearly are frustrated