Data Analyst SQL Interview Question | Normal vs Mentos vs Mentos PRO Life

In this video we are going to discuss a SQL interview problem asked in Spring Computer Technologies. We are going to solve the problem with 3 methods :

00:00 Understanding the problem
03:00 Normal Life Method
10:30 Mentos Life Method
17:06 Mentos PRO

Here is the script:
create table people
(id int primary key not null,
name varchar(20),
gender char(2));

create table relations
(
c_id int,
p_id int,
FOREIGN KEY (c_id) REFERENCES people(id),
foreign key (p_id) references people(id)
);

insert into people (id, name, gender)
values
(107,'Days','F'),
(145,'Hawbaker','M'),
(155,'Hansel','F'),
(202,'Blackston','M'),
(227,'Criss','F'),
(278,'Keffer','M'),
(305,'Canty','M'),
(329,'Mozingo','M'),
(425,'Nolf','M'),
(534,'Waugh','M'),
(586,'Tong','M'),
(618,'Dimartino','M'),
(747,'Beane','M'),
(878,'Chatmon','F'),
(904,'Hansard','F');

insert into relations(c_id, p_id)
values
(145, 202),
(145, 107),
(278,305),
(278,155),
(329, 425),
(329,227),
(534,586),
(534,878),
(618,747),
(618,904);

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[ankitbansal]

Launching the first weekend batch of Think SQL zero to hero live 6 weeks bootcamp. 🎉🎉

All you need to know about the bootcamp :

✅15+ LIVE sessions with lifetime access to recordings. (2 hours each)

✅ No pre-requisite required ( Learn From Absolute Scratch)

✅ Hand-picked SQL problems (asked in FAANG and product-based companies) set for practice.

✅ Includes 2 portfolio projects on real datasets

✅ Assignments after each class.

✅ Bonus worth Rs 5000. Access to a premium website for SQL practice.

✅ Access to premium community of data professionals. You can ask doubts here even after the course.

✅ Introductory session to Tableau to pursue various data roles within the industry.

And many more...

This is the only course you need for all your analytics needs.

Early bird offer of 24 percent discount available only for limited time. Use code EARLY24.

[ManpreetSingh-tvrw]

Thanks to your previous videos directly applied mentos pro approach

with cte1 as (select r.*, p.name as childname, p1.name as parentname, p1.gender from relations r inner join people p on p.id=r.c_id
inner join people p1 on p1.id=r.p_id),
final as(
select childname, parentname, gender from cte1)
select childname, max(case when gender='F' then parentname end) as mother,
max(case when gender='M' then parentname end )as father from final
group by childname

[MixedUploader]

Ankit Thanks for 3 methods. By learning from you was able to do with mentos pro solution directly
i.e., tried with 2 inner joins, one for child details and other for parents details & then filtered 'Father' 'Mother' with case when below:
with cte as(select cd.*, p.name as parent_name, p.gender as p_gender
from (select r.c_id, c.name as child_name, r.p_id
from relations r
inner join mc_people c on r.c_id=c.id) cd
inner join mc_people p on cd.p_id=p.id)
select child_name as child,
max(case when p_gender='M' then parent_name end) as father,
max(case when p_gender='F' then parent_name end) as mother
from cte
group by c_id, child_name

[Creatiiveminds]

Your videos are really helpful to learn SQL... thank you so much for your videos.. from basic to advanced i have become pro in it..

[pavitrashailaja]

Awesome way to explain the problem . You are the best❤

[souptikdas]

Sql server setup looks much better though. But great as always.

[Thetradersclub_]

Sir, your videos are amazing.
You may continue with the previous setup...as in this setup the black color of background makes it difficult for us to view in to low quality mode.
Thank you😊

[dfkgjdflkg]

set up is ok, we thank you for your work. Intentions count more.

[himanshu_duggal]

Camera setup is good but I like the SSMS better :)

[aayushibirla]

i did using mentos solution before seeing it.. haha all thanks to you that I am becoming an expert in SQl by watching all your videos

[akp-]

@ankitbansal6 best thing about your videos is you have given create/insert all statements. so anyone watching can practice along with your video. it very unique thing. thanks for the effort.

[UnrealAdi]

great setup! But, you must use a smaller window for the facecam!!!

[Ashu]

great setup. request to use white background please

[biswanathprasad]

i have built Mentos pro wala mindset. all thanks to you :)

[vanshhans]

Slowly getting to the solutions without watching the answer in the video.
All thanks to you ankit❤.
Here is my solution -
with children as (select c_id, min(name) as child_name from relations as r
inner join people as p
on r.c_id=p.id
group by c_id
),
parents as(select c_id, name as parent_name, gender from relations as r
inner join people as p
on r.p_id=p.id )
select child_name,
min(case when gender='M' then parent_name end) as father_name,
min(case when gender='F' then parent_name end) as mother_name
from children c
inner join parents p
on c.c_id=p.c_id
group by child_name

[fit]

So informative video... can you please tell which software you are using for screen recording... don't want background noise in my videos

[komalsunandenishrivastava]

Hi Ankit, awesome explanation, but i have approached the problem in a different way. Let me know if the solution is correct
With cte as (
Select id, name, gender, c_id, p_id from persons p inner join relations r on p.id=r.c_id and p.id=r.p_id)
Select case when c_id=p_id then name end) as child_name,
Case when p.id=id and gender= 'M' then name end) as father,
Case when p.id=id and gender='f' then name end) as mother
From cte;

[invincible]

Thank you so much for the solution. At first I have tried to solved, then I have watched your vide, I have found that I also have applied the Mentos solution thgouh in a little bit different way :
with find_parent as (select p.id as child_id, p.name as child, p_id as parent_id
from people p inner join relations r on p.id = r.c_id )
select fp.child, max(case when p.gender = 'M' then p.name end )as father,
max(case when p.gender='F' then p.name end )as mother from find_parent fp inner join people p on fp.parent_id = p.id group by fp.child ;

[DeepakBharti-hl]

it took me for half an hour, but i was able to solved


my answer :

with cte as (
select c_id ,
max(case when gender = 'M' then name end) as Father,
max(case when gender ='F' then name end) as Mother
from people
join relations r
on people.id = r.p_id
group by c_id )
select people.name, cte.father, cte.mother from people
join cte on cte.c_id = people.id

[vaibhavverma]

with cte as
(select
c_id,
max(case when p.id = r.c_id then p.name end) as Child,
max(case when p.id = r.p_id and gender = 'M' then p.name end) as Father,
max(case when p.id = r.p_id and gender = 'F' then p.name end) as Mother
from people p, relations r
group by c_id)

select child, father, mother from cte