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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@ankitbansal6]

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. Link to register : bit.ly/NamastesqlOctober

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

[@pavitrashailaja850]

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

[@ankitbansal6]

Thank you so much 😀

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

[@vanshhans5676]

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

[@ManpreetSingh-tv3rw]

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

[@aayushibirla2590]

me also lol

[@dfkgjdflkg]

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

[@biswanathprasad1332]

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

[@aayushibirla2590]

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

[@nishantdandwate9967]

Nice Solution....Keep it up

[@invincible9971]

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 ;

[@ankitkumarpathak8112]

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

[@himanshu_duggal]

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

[@souptikdas9461]

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

[@shalinimeena7863]

great videos, please keep making.

[@ankitbansal6]

More to come!

[@meghnasoni]

Incredible

[@himanshubhatt5514]

SQl server is best and you're a Star :-)

[@TheCraigie007]

Great as always , I do prefer SSMS , though.

[@avi8016]

Mentos life💯 Thankyou for the video sir!!

[@ankitbansal6]

My pleasure

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

[@ankitbansal6]

Sure. Thanks for the feedback!!

[@ChinweOge]

Thank you for sharing 💕

[@ankitbansal6]

Thanks for watching!

[@tanushreenagar3116]

Nice sir

[@user-mr1io5mx1f]

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

[@vaibhavverma1340]

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

[@satyamace]

So crisp but this is in a non-ANSI format. although I'm not sure. @ankit Sir please suggest.

[@UnrealAdi]

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

[@Ashu23200]

great setup. request to use white background please

[@fit1801]

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

[@umasharma6119]

with star as (select *,lead(name) over(partition by c_id order by gender) as mother, lead(name) over(partition by c_id order by gender desc) as father from relations as r left join people as p on r.p_id=p.id) select c_id,max(mother),max(father) from star group by c_id

[@fog2640]

finished watching

[@Sachin-kk3np]

In what conditions or to solve which problem we use CASE with MAX/MIN with string values in columns?

[@Nick-du9ss]

please use normal setup tutorial was great as always

[@ankitbansal6]

Noted!

[@KisaanTuber]

My approach: select pc.name as child, max(case when pp.gender = 'M' then pp.name end) as father, max(case when pp.gender = 'F' then pp.name end) as mother from relations r LEFT JOIN people pc on r.c_id = pc.id LEFT JOIN people pp on r.p_id = pp.id GROUP by 1;

[@vikramjitsingh6769]

Hey, You would have given a lot of SQL interviews. Wanted to check whether using Windows functions is normal in interviews or not. Cuz I use windows function a lot . Are there moments in which interviewer says don't use Window function, if yes are they rare or normal in frequency?

[@ankitbansal6]

It's absolutely fine to use window functions. Sometimes they can ask to solve using other approaches and you should be prepared for that as well.

[@sidindian1982]

@@ankitbansal6 sir how 2 make background dark mode .. pls make a short vedio .....

[@harishmkr]

sir can you please tell me website you are using to solve the questions

[@priyankapatil4413]

This is my approach to problem : with cte as (select r.c_id,p.name,r.p_id as fp, case when LEAD(r.c_id,1) over(partition by c_id order by p_id) = r.c_id then LEAD(r.p_id,1) over(partition by c_id order by p_id) end sp from relations r inner join people p on r.c_id = p.id) select cte.c_id,cte.name, case when p.gender = 'F' then p.name else p1.name end as 'MothersName', case when p1.gender = 'M' then p1.name else p.name end as 'FathersName' from cte inner join people p on cte.fp = p.id inner join people p1 on cte.sp = p1.id where cte.sp is not null;

[@007anantdeep]

Which sql editor is this? looks so simple!

[@Kondaranjith3]

thank you sir camera is good but if possible use ssms sir

[@ankitbansal6]

Ok next time👍

[@ramakrishnatirumala428]

Hi ankit... what is the secret to become a master in sql like you?? no love symbols ...need only suggestions.

[@ankitbansal6]

I have been using SQL almost everyday for the last 13 years 😊 There is no secret.

[@swethathiruppathy9973]

Hi Sir, SQL SERVER VIDEOS LOOKS BETTER THAN THIS BLACK SCREEN..HERE FONT SIZE IS VERY SMALL HERE BY SHARING MY SOLUTION with cte as(select name ,p_id from people p join relations r on p.id=r.c_id) select C.name,max(case when gender="M" then p.name end) as Father, Max(case when gender="F" then p.name end )as Mother from cte C JOIN PEOPLE p ON p_id =id group by C.name order by C.name asc ;

[@MohitSingh-ze8tb]

Do you provide hands on experience on data base as well? I'm looking for a free source where i can write and practice the queries. Is this provided in your Bootcamp?

[@ankitbansal6]

Yes you will have premium access to a website to practice SQL problems

[@MohitSingh-ze8tb]

@@ankitbansal6 what about when we take your pre recorded videos? How one can practice queries?

[@shivendrapatel443]

My solution: select p1.name as child, max(case when p2.gender = 'M' then p2.name else null end) as father, max(case when p2.gender = 'F' then p2.name else null end) as mother from relations r join people p1 on r.c_id = p1.id join people p2 on r.p_id = p2.id group by p1.name;

[@The__Gopal]

Hello Sir, I was asked to find the 2nd highest salary without using window function. There was 2 table 1st table had employee details and another table had salary and department details. Please try to solve this question. Thanks :)

[@ankitbansal6]

Sure send me the problem statement on [e-mail]. I will lose the track here .

[@sirishaanjali8521]

Hi Ankit, I have one SQL problem will us b able to solve it. I need distinct count of employees till date compared with previous rows and current row but we cannot use count and distinct once while using over clause how to solve it Input is Stffid date. custid 101. Sept1 301 101. Sept2 301 101 sept3 301 101 sept4 302 101 sept5 304 101 sept6 304 101 sept7 305 101 sept8 305 101 sept9 301 101 sept10 301 Out put be like input with extra column customers handled till date Cist handled 1 1 1 2 3 3 4 4 4 Same input with

[@milindzuge906]

Hi Ankit Sir, Awesome explanation ❤, could please also let us know which SQL IDE you have use this time for explaining the solution

[@ankitbansal6]

IntelliJ

[@milindzuge906]

@@ankitbansal6 Thank you sir

[@vandanaK-mh9zo]

My Approach to problem(before watching any of your solution): with child_cte as ( select id as c_id, name as Child from people where id in (select distinct c_id from relations)), parents_cte as ( select r.c_id,r.p_id, case when p.gender = 'F' then name end as mother, case when gender = 'M' then name end as father from people p inner join relations r on r.p_id = p.id ) select c.Child, min(p.father) as father, min(p.mother) as mother from parents_cte p inner join child_cte c on p.c_id = c.c_id group by child; Mentos Pro : select c.name, max(case when p.gender = 'F' then p.name end) AS mother, max(case when p.gender='M' then p.name end )as father from relations r inner join people p on r.p_id = p.id inner join people c on r.c_id = c.id group by 1; Thank you so much Ankit sir. Your videos are amazing and with consistent practice, Nowadays, I am able to solve these problems without watching your solutions.

[@rahulmehla2014]

my approach: with cte as( select p.id,p.name as child,p1.name as parents,p1.gender as p_gndr from people p inner join relations r on p.id = r.c_id inner join people p1 on p1.id=r.p_id) select child, max(case when p_gndr = "M" then parents end) as Father, max(case when p_gndr = "F" then parents end) as Mother from cte group by child

[@user-yp5tj2ce3s]

select p.name as children, max(case when q.gender = 'm' then q.name end) as Father, max(case when q.gender = 'f' then q.name end) as Mother from people p join relations r on p.id = r.c_id join people q on q.id = r.p_id group by p.name

[@learnwithme2644]

with cte as (select r.c_id, p.gender as g,p.name as parent from people p join relations r on r.p_id=p.id) select p.name as child, min(case when g='M' then parent end) as father, min(case when g='F' then parent end) as mother from cte join people p on cte.c_id=p.id group by p.name;

[@VISHALSINGH-jw2nn]

got the solution as below... with cte as(select r.c_id,r.p_id,p.name,p.gender from people p join relations r on p.id=r.p_id ) ,cte1 as(select r.c_id,r.p_id,p.name as c_name,p.gender as c_gender from people p join relations r on p.id=r.c_id ) ,cte2 as(select c.p_id,c.name,c.gender,c1.c_id,c1.c_name,c1.c_gender from cte c join cte1 c1 on c.p_id=c1.p_id ) ,cte3 as(select c_id,c_name as child,case when gender='M' then name end as father, case when gender ='F' then name end as mother from cte2 ) select child,max(father) as father ,max(mother) as mother from cte3 group by c_id,child

[@mr.pingpong502]

is the below query efficient or the worst possible method to do get the solution with cte as ( select d.p_id,name as parent,gender,c_id,child from people c left join (select c_id,p_id,name as child from people a inner join relations b on a.id=b.c_id) d on c.id=d.p_id where child is not null) ,cte2 as(select child,c_id,gender,case when gender='F' then parent else null end as Mother from cte where gender='F') ,cte3 as(select child,c_id,case when gender='M' then parent else null end as Father from cte where gender='M') select z.child,mother,father from cte2 z inner join cte3 y on z.c_id=y.c_id

[@anirvansen2941]

MYSQL with base as ( select a.child,b.name as parent_name,b.gender from ( select p.name as child,r.p_id from relations r inner join people p ON r.c_id = p.id) a INNER JOIN people b on a.p_id = b.id), father as( select child, parent_name as parent from base where gender = 'M' ), mother as ( select child, parent_name as parent from base where gender = 'F' ) select a.child,a.parent as father,b.parent as mother from father a join mother b on a.child = b.child order by child

[@monasanthosh9208]

With CTE as (Select Child_Name,Case When Gender="M" then Parent_Name end as Father_Name, Case When Gender="F" then Parent_Name end as Mother_Name from (Select R.C_id,P.Name as Child_Name,P1.Name as Parent_Name,P1.Gender from Relations R Left Join People P on R.C_id=P.id Left Join People P1 on R.P_id=P1.id)N), CTE1 as (Select Child_Name,father_Name from CTE Where Father_Name is not Null), CTE2 as (Select Child_name,Mother_name from CTE where Mother_Name is not Null) Select C.Child_name,C.Father_Name,C1.Mother_name from CTE1 C join CTE2 C1 on C.Child_Name=C1.Child_name order by C.Child_Name;

[@sushantsinghpal6323]

Sir ji aap ya to Mike le lo ya fhir Mike ko collar pr lga lo..kyuki apki voice bahut low rahti hai volume full krne pr bhi ache se nii aati..

[@hydergouri7826]

Camera is good , but for IDE i would suggest SSMS only.

[@numankhan2645]

I install ssms 19 but when I import data there is a pop up message something missing in your local system I watch tons of video but didn’t solve the problem second I when I run a query say your query run successfully but It didn’t show the result panel so then I used mYsql workbench and bigquery

[@dwaipayansaha4443]

My Solution: -- father table with cte as( select p.name child, t1.name father from (select * from people p join relations r on p.id=r.p_id where gender='M') t1 join people p on t1.c_id=p.id order by child), -- mother table cte1 as (select p.name child, t1.name mother from (select * from people p join relations r on p.id=r.p_id where gender='F') t1 join people p on t1.c_id=p.id order by child) select cte.child,father, mother from cte join cte1 on cte.child=cte1.child order by child

[@Ankitatewary-q6w]

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

[@udayakumark1079]

with mo as(select c_id,p_id,name,gender from relations r left join people p on r.p_id=p.id where gender='M'), -> fa as(select c_id,p_id,name,gender from relations r left join people p on r.p_id=p.id where gender='F'), -> ch as(select c_id,name from relations r left join people p on r.c_id=p.id group by c_id) -> select ch.name as child,fa.name as father,mo.name as mother from ch left join mo on ch.c_id=mo.c_id left join fa on ch.c_id=fa.c_id;

[@chiranjitdey3788]

I am not cleared of this question

[@shivaniyadav2545]

Pls switch to SQL server, this is not comfortable to look at

[@_anupamchauhan]

WITH cte AS ( SELECT p.id, p.name,LAG(people.name, 1) OVER (PARTITION BY p.id ORDER BY p.id) AS mother, people.name AS father FROM people p JOIN relations r ON p.id = r.c_id JOIN people ON r.p_id = people.id ), cte2 AS ( SELECT id, name as child, mother, father FROM cte ) SELECT child,mother,father FROM cte2 where mother is not null;