Solving SQL Interview Query | Find Tree Node type using SQL

In this video, let's solve an SQL interview query taken from LeetCode.
The problem might seem complex at first but there is a very simple solution to solve such problems so lets explore it in this video.

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

FTC disclaimer: This video was sponsored by Skillshare.

THANK YOU,
Thoufiq

[arjundev]

Damn. This was asked in my ANZ interview which I couldn't answer this clearly. Thanks Toufiq for the clarity that you provided.

[shaikusman]

Awsome brother....Loved the way you approach problem statement....Respect from Bangalore, INDIA.

[chimadivine]

I decided to solve it before watching your solution, and I actually solved it right.
The difference is that, in the second 'when' statement, I didnt add:
"p is not NULL"
And I think it will still work correctly like that.
I'm happy that my solution was same as the solution of a genius like you.

Many thanks for this quality video!

[sandeepreddy]

Thanks Toufiq for the effort you put in making these videos, I was actually waiting for a video on hierarchical queries. Another way if it helps others

SELECT id, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 'Leaf'
WHEN CONNECT_BY_ISLEAF = 0 AND LEVEL = 1 THEN 'Root'
WHEN CONNECT_BY_ISLEAF = 0 AND LEVEL <>1 THEN 'Inner'
END typ
from tree
connect by prior id = p_id
start with p_id IS NULL;

[muthu]

The way you explained the problem I really stunned

[alisonhenley]

Excellent! Feeling a lot more prepared to take on interviews because of you!!!!

[arshadmohammed]

way simple way to explain ! Thanks Taufiq

[CassStevens]

your solution is simpler than mine:
select id, 'root' as type
from tree
where p_id is null
union
select distinct p_id as id, 'inner' as type
from tree
where p_id <> (select id from tree where p_id is null) and p_id is not null
union
select id, 'leaf' as type
from tree
where id not in (select distinct p_id from tree where p_id is not null) and
p_id is not null

[satheeshkumarak]

Hello Sir @techTFQ,

Can you make a video on working with JSON & Nested JSON data ?

[raitup]

Hi Taufiq, I think I have the next level of this excercise, actually is a real problem in a Pharmaceutical company. I was wondering if I can send you the the problem with extra complications and if you consider that the problem worth, would you mind solve it in a video? That would be nice

[Zenoid_]

Thanks a lot for doing these kind of videos❤️

[dilipinamdar]

Thanks a lot Taufiq, Please suggest books to read about MS SQL.

[avi]

Thanks a lot Taufiq,
One question it should be possible to approach the same problem via self join

[nikhilavemuri]

Hi Thoufiq,

Waiting to join your online training. Please let me know when your next live training batch will get started.

[santhiv]

Hi, Your vedios are very useful and informative y don't you start oracle plsql topics also it will help to get logic how sql queries used to plsql as well

[Iamabhay_]

Sir, please-please makes a video on Database administrator vs Data science

[RAHUDAS]

Can we build custom function for same, kindly make video on custom functions

[GeekSP]

@techTFQ in practice, why do we find the tree node type? i mean are there any use cases you can mention??
I appreciate spreading value

[Tusharchitrakar]

Hi, firstly you have great pedagogical technique of explaining and kudos to you.
Secondly, I was trying on my own before seeing your video and I used the exact same technique to derive the result but I wasn't happy for one reason. It's because of how the IN operator works: it does an entire table scan. For example, if I'm searching for id 3 in the p_id column, it will still keep searching till the end of the p_id even if it finds 3 earlier. Hence, one can imagine how inefficient this approach is for extremely large trees with thousands of nodes. I tried using an index too but still the query cost was not reduced. Is there something like a WHERE EXISTS that we can use where the search will stop as soon as I find the target (in this case 3 in p_id) instead of going till the end of the inner subquery table?
Kindly give your input, thanks!

[sujathav-rfvt]

when you will start your next batch ? and is there any offer for new year for our course
?