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

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

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

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

[@pavitrashailaja850]

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

[@ankitbansal6]

Thank you so much 😀

[@dfkgjdflkg]

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

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

[@souptikdas9461]

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

[@biswanathprasad1332]

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

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

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

[@shalinimeena7863]

great videos, please keep making.

[@ankitbansal6]

More to come!

[@himanshu_duggal]

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

[@avi8016]

Mentos life💯 Thankyou for the video sir!!

[@ankitbansal6]

My pleasure

[@nishantdandwate9967]

Nice Solution....Keep it up

[@akp7-7]

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

[@Ashu23200]

great setup. request to use white background please

[@UnrealAdi]

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

[@himanshubhatt5514]

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

[@CheatingStoriesWithChinwe]

Thank you for sharing 💕

[@ankitbansal6]

Thanks for watching!

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

[@DeepakBharti-h9l]

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.

[@TheCraigie007]

Great as always , I do prefer SSMS , though.

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

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

[@ajaycheryala7760]

Hey Ankit, which sql you are using in this video, where can I get it?

[@tanushreenagar3116]

Nice sir

[@anantdeep5]

Which sql editor is this? looks so simple!

[@Sachin-kk3np]

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

[@meghnasoni]

Incredible

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

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

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

[@SidIndian082]

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

[@komalsunandenishrivastava9211]

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;

[@harishmkr]

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

[@vinothkumars7421]

HI Ankit, may i know the IDE Name? its very clean

[@Kondaranjith3]

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

[@ankitbansal6]

Ok next time👍

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

[@anirbanbiswas7624]

select c_id,max(case when r.p_id=p.id and p.gender='M' then p.name end) as father_name, max(case when r.p_id=p2.id and p2.gender='F' then p2.name end) as mother_name from relations as r join people as p on p.id=r.p_id join people as p2 on p2.id=r.p_id group by 1

[@KapilKumar-hk9xk]

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 inner join relations on p_id = id group by c_id) select name, father, mother from cte inner join people on c_id = id ;

[@harshitkesarwani1750]

sir your previous setup was better as we could see multiple tables in a single frame in ssms

[@ankitbansal6]

Okay

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

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

[@RamiKaur-n7h]

my approch is to solve this select c.name as child_name, max(case when p.gender='F' then p.name else null end) as mother , max(case when p.gemder='M' then p.name else null end) as father from relations rl left join people p on r.p_id = p.id inner join poeple c on r.c_id =c.id group by child_name

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

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

[@gowrikm-n5u]

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

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

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

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

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

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

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

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

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

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

[@chiranjitdey3788]

I am not cleared of this question

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

[@yashsaini9494]

WITH fatherTable AS ( SELECT c_id, p_id, gender, name AS fatherName FROM people INNER JOIN relations on p_id = id WHERE gender = 'M' ) , motherTable AS ( SELECT c_id, p_id, gender, name AS motherName FROM people INNER JOIN relations on p_id = id WHERE gender = 'F' ) , childTable AS ( SELECT c_id, name AS childName FROM people INNER JOIN relations on c_id = id ) SELECT childName,fatherName, motherName FROM fatherTable INNER JOIN motherTable on fatherTable.c_id = motherTable.c_id INNER JOIN childTable on childTable.c_id = fatherTable.c_id GROUP BY childName,fatherName, motherName

[@udaya31]

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;

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

[@syedzeeshan4742]

WITH cte AS (SELECT b.name AS parent,b.gender,b.c_id,c.name AS child FROM (SELECT e.*,a.* FROM relations e INNER JOIN people a ON e.p_id=a.id) b INNER JOIN people c ON b.c_id=c.id) SELECT child ,max(CASE WHEN gender='M' THEN parent END) AS father , max(CASE WHEN gender='F' THEN parent END) AS mother FROM cte GROUP BY child ;

[@jonedrive7268]

Video quality is pathetic.

[@ankitbansal6]

Change the video quality from KZbin video settings

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

[@HARSHRAJ-wz2rp]

with cte as( select DISTINCT(c_id) as c_id FROM relations ), cte1 as( select cte.c_id,name as child_name FROM cte JOIN people ON cte.c_id=people.id ),ct2 as( select relations.c_id,people.* FROM relations JOIN people ON relations.p_id=people.id ),ct3 as( select ct2.*,ROW_NUMBER()OVER(PARTITION BY c_id) as x1 FROM ct2 ),cte4 as( select c_id,id,name as father_name,gender,x1 FROM ct3 where gender='M' ),cte5 as( select c_id,name as mother_name,gender,x1 FROM ct3 where gender='F' ),cte6 as( select cte4.c_id,father_name,mother_name FROM cte4 JOIN cte5 ON cte4.c_id=cte5.c_id ) select child_name,father_name,mother_name FROM cte6 JOIN cte1 ON cte6.c_id=cte1.c_id;