Data Analyst SQL Interview Question | Normal vs Mentos vs Mentos PRO Life
Вставка
- Опубліковано 11 лип 2024
- 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
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
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..
set up is ok, we thank you for your work. Intentions count more.
Awesome way to explain the problem . You are the best❤
Thank you so much 😀
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
Nice Solution....Keep it up
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
me also
lol
Incredible
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
Sql server setup looks much better though. But great as always.
Camera setup is good but I like the SSMS better :)
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
i have built Mentos pro wala mindset. all thanks to you :)
@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.
great videos, please keep making.
More to come!
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 ;
Great as always , I do prefer SSMS , though.
Nice sir
So informative video... can you please tell which software you are using for screen recording... don't want background noise in my videos
In what conditions or to solve which problem we use CASE with MAX/MIN with string values in columns?
Thank you for sharing 💕
Thanks for watching!
great setup! But, you must use a smaller window for the facecam!!!
great setup. request to use white background please
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
So crisp but this is in a non-ANSI format. although I'm not sure. @ankit Sir please suggest.
Which sql editor is this? looks so simple!
Mentos life💯
Thankyou for the video sir!!
My pleasure
sir can you please tell me website you are using to solve the questions
SQl server is best and you're a Star :-)
finished watching
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😊
Sure. Thanks for the feedback!!
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
sir your previous setup was better as we could see multiple tables in a single frame in ssms
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
please use normal setup
tutorial was great as always
Noted!
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?
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.
@@ankitbansal6 sir how 2 make background dark mode .. pls make a short vedio .....
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
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;
Hi ankit... what is the secret to become a master in sql like you?? no love symbols ...need only suggestions.
I have been using SQL almost everyday for the last 13 years 😊 There is no secret.
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;
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?
Yes you will have premium access to a website to practice SQL problems
@@ankitbansal6 what about when we take your pre recorded videos? How one can practice queries?
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 :)
Sure send me the problem statement on sql.namaste@gmail.com. I will lose the track here .
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
thank you sir camera is good but if possible use ssms sir
Ok next time👍
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
;
Hi Ankit Sir, Awesome explanation ❤, could please also let us know which SQL IDE you have use this time for explaining the solution
IntelliJ
@@ankitbansal6 Thank you sir
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;
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;
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
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.
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..
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
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
I am not cleared of this question
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;
Camera is good , but for IDE i would suggest SSMS only.
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
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
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;
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;
Pls switch to SQL server, this is not comfortable to look at
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
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;