Solving an SQL Interview Problem | Find Child - Parent - Grandparent Hierarchy using SQL

In this video, we will solve an SQL Interview problem where we need to find the child, parent, and grandparent hierarchy/relationship to arrive at the final result. The problem is very interesting and slightly confusing.
This is a good SQL problem to test out your SQL Query writing skills.

This video is sponsored by OdinSchool.

Download Dataset, SQL scripts for free from my blog below:

Timelines:
00:00 Intro
00:20 Understanding the problem statement
01:14 Analysing the given data
04:36 OdinSchool Bootcamp promo
06:20 Solution to the problem

Thanks for watching!
Thoufiq | techTFQ

[attilapekkaszabo-ivtl]

Best tutor for SQL, easy to follow and very detailed explanation. Must follow and subscribe if you want to solve interesting SQL problems.

[Madhusudan_Sarkate]

Best explanation ever hard problems look like simple

[sujaa]

One of the best YouTube channel for SQL! Thanks TFQ!

[zewduwereta]

Very interesting problem! I like the way you dissect the problem into different parts, it makes easier to approach it! Thanks!👍👍

[xntuqnr]

JazakAllah.
Very convenient solution to a complex problem. May Allah bless you.

[Samcrazyu]

Thanks toufiq Bhai, for helping Data community and and sharing knowledge & experience 😊, it helps a lot to understand self join

[soundarsswag]

Select count(1) from person a
Where status =Alive
And exists
( select 1 from persons
Where a.parent = b. Person)

[SrikanthSSSSSS]

Thank you sir, I developed my SQL with your videos only

[reachbharathan]

I think this problem should be the best example for demoing the usecase for self join

[krantikumar]

Self join is the best way to answer this question

[venkeenkokku]

This correlated sub-query will yield the result as required ---
select c.person from persons c where exists (
select null from persons p where c.parent = p.person and exists (
select null from persons g where g.person= p.parent
and g.status = 'Alive'))

[avi]

Great explanation of self join
Thankyou sir for this video!

[AviralOjha]

May Allah always be by your side my brother. You are like one of his greatest blessings on 🌎❤ The way you have explained and brought the best solution out is commendable! Ankit Bansal makes things over complicated, neither do I enjoy his style of teaching. We are meant to learn from you!

[reachbharathan]

Thanks a lot, I understood everything, great explanation,
i question what the last part select count( 1), are we doing a grouping?

[hviqurl]

great video as always, it was well explained.

[Krishna]

Thank you for sharing, nice explanation. When will you start postgresql Boot camp

[HARSHRAJ-wzrp]

select * from persons;
with cte as(
select P2.person, P1.parent as "Grandfather" FROM persons P1 JOIN persons P2 ON P1.person=p2.parent
)
select COUNT(*) FROM cte JOIN persons ON where person_status="Alive";
hoto gaya per kaise gaya woh nahi pata

[swethaveluguri]

I'm facing a SQL interview question repeateadly. "Why a stored preocedure is executing fine till yesterday and today it is taking long time to execute" [indexes are not dropped/no changes to this query]. Can you please explain this question ?

[sachingunjal]

hope this solution is acceptable --

select P1.person as self, P2.person as Parent, P3.Person as GP, P1.person_status as GP_Status
from Persons as P1
Right join Persons as P2 on P1.parent = P2.Person
right join Persons as P3 on P2.parent = P3.Person

where P1.person_status = 'Alive'

[swapnilsolanki]

Another way of writing query for same problem

with recursive cte1 as
(
select *
from test
where par_name in (select p_name from test)
),
cte2 as
(
select *, 1 as level
from cte1
union
select c1.*, level + 1 as level
from cte1 c1
join cte2 c2
on c2.par_name = c1.p_name
)
select count(1)
from (
select
cte2.p_name,
cte2.par_name,
t.status
from cte2
join test t
on cte2.par_name = t.p_name
where level = 2
and t.status = 'Alive'
) subquery