PWC SQL Interview Question for a Data Analyst Position | SQL For Analytics
HTML-код
- Опубликовано: 22 дек 2023
- In this video we will solve a SQL interview problem asked in PWC. We will solve it using 2 methods. Here is the script :
create table source(id int, name varchar(5))
create table target(id int, name varchar(5))
insert into source values(1,'A'),(2,'B'),(3,'C'),(4,'D')
insert into target values(1,'A'),(2,'B'),(4,'X'),(5,'F');
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 #dataanalytics
Please do hit the like button on the video for more interview questions.
SELECT
COALESCE(a.id,
b.id) AS id,
CASE WHEN a.name IS NULL THEN 'New in target'
WHEN b.name IS NULL THEN 'New in Source'
ELSE 'Mismatch' END AS comment
FROM source AS a
FULL OUTER JOIN
target AS b
ON a.id = b.id
WHERE a.Name b.Name OR a.id IS NULL or b.id IS NULL;
best
You are the best mentor for me👏🙏
A big fan of your SQL play list.. Really helpful.. Thank you so much..
Glad to help 😊
Hey man, great video. I'm a BI developer, and i still like yo watch tutorials to learn new stuff or see if i already know how to solve the problem as i still consider myself somewhat new to professional coding. Definitely subscribing and keep up the good content!
Hi dad ur using my account-
Cool, thanks!
Thanks for bringing your unique perspective while solving the problems!!!
My pleasure!
Thanks Ankit, solved this in one go. Your vidoes are quite helpful.
Most welcome 😊
I was asked the same question during the Nineleaps interview, and I successfully solved it.
with cte as(
select s.id as s_id,s.name as s_name,t.id as t_id,t.name as t_name
from source s
full outer join target t on s.id = t.id
where s.id is null or t.id is null or s.name != t.name)
select coalesce(s_id,t_id), case when t_id is null then 'new in source'
when s_id is null then 'new in target'
else 'Mismatch' end as comment
from cte
This was my approach
Sir, my query is as follows:
with cte as
(
select a.id a_id, a."Name" a_name, b.id b_id, b."Name" b_name
from source a
full outer join target b
on a.id = b.id
)
select case when a_id is not null then a_id else b_id end id,
case when a_name is not null and b_name is null then 'New Source'
when a_name is null and b_name is not null then 'New Target'
else 'Mismatch' end comment
from cte
where (a_id, b_id) not in(select a_id, b_id from cte where a_name = b_name)
You have earned a new subscriber 🎉
You have big fanbase in our company Ankit.. Every time when new joinee came, blindly i am recommending your content only and they become your fan❤❤❤..
Thank you. Means a lot to me ❤️
Hey Ankit
I solved it using below query. It uses window function:
with all_id as (
select id, name, 'source' as flag from source union all select id,name, 'target' as flag from target),
flag as (
select *,
count(1) over (partition by id,name) as cnt_1,
count(1) over (partition by id) as cnt_2
from all_id)
select
id,
max(case when cnt_1 =cnt_2 and flag='source' then 'New in Source'
when cnt_1=cnt_2 and flag='target' then 'New in target'
else 'Mismatch' end) as Comment
from flag where cnt_1!=2
group by id;
Hi Ankit, thanks for making SQL very easy. Just one request,
Can you please make video on how to approach a problem in interviews? Because in interviews we have to write query in notepad and we can't run the parts of the query. We have to visualise everything while writing. If you will make this video, It will be very useful for everyone.
THANKS
That will come with practice 🙂
Great
Today you have earned a new subscriber 🎉
Yay! Thank you!
with sample1 as (
select * from source
minus
select * from target
),
sample2 as (
select * from target
minus
select * from source
)
select coalesce(s1.id,s2.id) as id
,case
when s1.name s2.name then 'its a mismatch'
when s1.name is null then 'new in target'
when s2.name is null then 'new in source'
end as "comment" from
sample1 s1 full join
sample2 s2 on
s1.id=s2.id
Hi Ankit... Thanks for the great videos.Along with multiple solutions for the query if you can explain the performance of all solutions..that would be great.As a developer I also need to write efficient queries.
Without checking your solution i tried very old way ..
select id,name ,'New in source' from source where id not in (select distinct id from target )
UNION ALL
select id ,name ,'New in Traget' from target where id not in (select distinct id from source )
UNION ALL
select s.id ,s.name ,'MisMatch' from source as s join target as t on s.id=t.id and s.name!=t.name
order by id
After checking solution i realized there are lot of improvement needed.
Thanks
(select id, 'new in source' as comment from source where id not in (select id from target))
union
(select id, 'new in target' as comment from target where id not in (select id from source))
union
(select s.id, 'mismatch' as comment from source s join target t on s.id = t.id and s.name != t.name)
great....
I am a fan too, we just don't spend time to express a thank you for all you have tought
🙏
2nd one is awsome
I'm curious what the explain plans look like for both these queries.
with cte as (
select *,'Source' as Location from source where id not in (select a.id from source a inner join target b on a.id=b.id and a.name=b.name)
union all
select *,'target' as Location from target where id not in (select a.id from source a inner join target b on a.id=b.id and a.name=b.name)
)
select distinct a.id,case when occurance>1 then 'Mismatch' when lower(Location)='source' then 'New in Source' when lower(Location)='target' then 'New in Source' else null end as comments
from cte a inner join (select id,count(id) as occurance from cte group by id )b on a.id=b.id
💯💯❤
Done
Hi Ankit Sir, I am working as a customer service professional and my day is an Uber Driver, I really don’t want him working so much at this age and I wanna support my family financially. I have been following your videos and have learnt a lot of SQL, I want to master this skill for my life, please suggest me a course of yours which will help me. I want to tell SQL as my Strength in all of the interviews. I want a live class. Please suggest
Live class will be in March. For now you can take the recorded course from namaste SQL , zero to hero one. Later if you want you can attend live classes by paying the difference.
Thanks for suggesting, but I wanna inform that I know a little SQL Basics, but I get confused with Joins and complex problem, do I have to take Zero to Hero SQL recorded, right?@@ankitbansal6
HI ankit,
Thank you for another excellent sql solved problem
Everytime i am watch your videos makes me confidence.
once we see sql query instantly how we think this method we can use it for this query?
there where i lack. Please advice...Thank you
Practice practice practice. Solve all my questions at least 2 times without checking the solution
@@ankitbansal6 Thank you!🙂 Mentor
Hi Ankit,
Below query is also correct for this solution, right?
Please correct me if i am wrong.
select a.id, comment
from
(select coalesce(s.id,t.id) as id,
CASE when t.id is null then 'New in Source'
when s.id is null then 'New in Target'
when t.name != s.name then 'Mismatch'
END AS Comment
from source s
full join target t on s.id = t.id) a
where a.comment is not null;
Thanks!
PySpark Approach and Solution explanation video for this problem:
ruclips.net/video/JlPG-oIAK2E/видео.htmlsi=BptXwtPBjrHUrhQ4
some more challenges please.
Here you go
Complex SQL Questions for Interview Preparation: ruclips.net/p/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
with cte as (
select id, name ,case when id is not null then 'new source' end as common from source
where id not in (select id from target)
union all
select id, name, case when id is not null then 'new target' end as common from target
where id not in (select id from source)
union all
select a.id, a.name, case when a.id is not null then 'is mismatch' end as common from target a join source b
on a.id = b.id
where a.name != b.name
)
select distinct id , common from cte
This will done using case statement?
Hi Ankit, i would like to be excel in sql hence planing to take your course therefore could you please let me know about the process of the same and also ur courses are recorded sessions or live one ?
Recorded
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
What experience-level Data Analysts can expect such questions Ankit?
3 plus years
Me being able to solve such problems with 1.25yrs…credits to namaste sql 😉
with cte_1 as(
select id
from source
where id not in (select id from target)),
cte_2 as(
select id
from target
where id not in (select id from source)),
cte_3 as(
select id
from source
where id in (select id from target) and name not in (select name from target))
select id,'new in source' as "comment"
from cte_1
union
select id,'new in target' as "comment"
from cte_2
union
select id,'mismatch' as "comment"
from cte_3
bhai , kya solution nikala salute . Jo data chahiye wo seggregate krke label lga do .
with
cte_new_in_source as (select id,'New in Source' from source where id not in (select id from target)),
cte_new_in_target AS (select id,'New in Targer' from target where id NOT IN (Select id from source)),
cte_mismatch as (select s.id, 'Mismatch' from source s inner join target t on s.id=t.id and s.name!=t.name)
select * from cte_new_in_source union select * from cte_new_in_target union select * from cte_mismatch
Sir, my query is as follows:
with cte_1 as
(Select A.id as source_id, B.id as target_id,
CASE WHEN B.name is null THEN 'New in Source' WHEN A.name is null THEN 'New in Target' ELSE 'Mismatch' END as Comment
FROM source A
FULL OUTER JOIN target B
ON A.id = B.id
WHERE A.name!=B.name OR A.name is null OR B.name is null)
Select * from
(Select source_id as id, Comment from cte_1
UNION
Select target_id as id, Comment from cte_1)xyz
WHERE id is not null;
You can do simply with full outer join single query. Check my solution.
@@ankitbansal6 Yes sir...I checked....
Sir any suggestions for fresher in sql.
I completed these topics DDL, DML, DCL, TCL and also join.
Join , aggregation, case when
my query got a bit lengthy because I considered names first, then had to change for ID
with cte as ( select s.id as sid, s.name as sn,t.id as tid, t.name as tn from source as s
full join target as t on s.id = t.id)
, cte2 as (select sn, tn, sid, tid,
case when sn = tn then 'existing'
when sn != tn then 'mismatch'
when sn is not null and tn is null then 'new in source'
when sn is null and tn is not null then 'new in taret'
end as outs
from cte)
SELECT sid, outs FROM cte2
WHERE outs != 'existing' and sid is not null
UNION
SELECT tid, outs FROM cte2
WHERE outs != 'existing' and tid is not null
Hello Ankit this is my solution. Please tell how it is
select id,'New In Source' Comment from source
where id not in (select id from target)
UNION ALL
select id,'New In Target'Comment from target
where id not in (Select id from source)
UNION ALL
select source.id,'MISMATCH'Comment from source LEFT JOIN target ON source.id=target.id
where source.name not in(Select name from target) and source.id in (select id from target)
Why min(table_name) gives 'target' instead of 'source' if we are seeing it lexicographically
select id,'New in source' as comment from source
where id not in(select id from target) and name not in (select name from target)
union
select id,'New in target' as comment from target
where id not in(select id from source) and name not in (select name from source)
union
select id,'Mismatch' as comment from source
where id in(select id from target) and name not in (select name from target)
union
select id,'Mismatch' as comment from target
where id in(select id from source) and name not in (select name from source)
It's really a big solution. You can do it just with a simple full join. Watch the video. Good attempt though 👍
@@ankitbansal6 ok sir❤️ …thank you so much
where s.name != t.name or s.name is null or t.name is null , Isn't it better to use "where s.name is distinct from t.name" ??
easy way of doing :-
with cte as
(select id,name from source
union all
select id,name from target)
select distinct id,comments from(select id,case when id=3 then 'new in source'
when id=5 then 'new in target'
when id=4 then 'mismatch'
end as comments from cte)
where comments is not null;
another very easy approach using diff join
select t1.id,'new in source' as comments
from source t1
left outer join target t2
on t1.id=t2.id
where t1.id=3
union all
select t2.id,'new in target' as comments
from source t1
right outer join target t2
on t1.id=t2.id
where t2.id=5
union all
select t1.id,'mismatch' as comments
from source t1
join target t2
on t1.id=t2.id
where t1.id=4;
You serious bro? 😂😂
they r not dynamic@@reachrishav
Solution for MYSQL
with cte as (
select *, concat(id,name) as ct, 'ent_sou' as col_table from source
union all
select *, concat(id,name) as ct, 'ent_target' as col_table from target
)
select distinct id,
case when (count(col_table) over (partition by id order by id)) =1 then col_table
else 'mismatch' END AS comment
from cte
where ct not in (select concat(id,name) from target)
or ct not in (select concat(id,name) from source)
Here is my solution :
With CTE as(
Select s.id as s_id, s.name as s_name, t.id as t_id, t.name as t_name
from source s
FULL outer join target as t
on s.id = t.id)
Select
CASE
when s_id is null then t_id else s_id end as id,final_status
from (
Select *,
CASe when s_name = t_name and s_id = t_id then 'All_Match'
When s_name != t_name and s_id = t_id then 'Name_Mismatch'
when s_id is null and t_id is not null then 'New_in_target'
when s_id is not null and t_id is null then 'New_in_Source'
END as final_status
from CTE) as a
Where final_status != 'All_Match'
with cte as (select s.id as s_id, s.name as s_name,
t.id as t_id, t.name as t_name
from source s
full join target t
on s.id = t.id)
select * from(
select coalesce(s_id,t_id) as id,
case when t_id is null then 'new in source'
when s_id = t_id and s_name t_name then 'mismatch'
when s_id is null then 'new in target'end as comment
from cte)a where comment is not null
how can we execute this query in mysql work bench as full outer join is not working
Use second approach
do left join first and then right join and do a union all. you should see both common and un-common records.
ALTERNATIVE WAY (Joins, Union ,CASE)
SELECT
id,
CASE
WHEN Id = 3 THEN 'New in Source'
WHEN ID = 5 THEN 'New in Target'
WHEN ID = 4 THEN 'Mismatch'
ELSE 'Not Found'
END AS Comment
FROM
(
select * FROM Source where id = 3
UNION
select * from Target Where id = 5
UNION
select * from Source where id = 4
) AS alias_table
The first solution is easy but while using MYSQL full outer join is not available......what can be done in MYSQL?
First left join then union with right join. Hope you figured it out before itself
Hello, in a hurry, if I was in an interview, I would have answered like this - this is the first thought:
with notinsource as (select a.id, case when not exists(select * from target where a.id=id )then 'new in source' end as 'comments'
from source a
where case when not exists(select * from target where a.id=id )then 'new in source' end= 'new in source' )
, notintarget as (select a.id, case when not exists(select * from source where a.id=id )then 'new in target' end as 'comments'
from target a
where case when not exists(select * from source where a.id=id )then 'new in target' end= 'new in target' )
, missmatch as(select a.id , case when exists(select * from target where a.id=id and a.namename)then 'missmatch' end as 'comments'
from source a
where case when exists(select * from target where a.id=id and a.namename)then 'missmatch' end='missmatch' )
select * from notinsource
union all
select * from notintarget
union all
select * from missmatch
That's very long. I think your first click during the interview should be full outer join.
@@ankitbansal6 I will follow your advice.thank you
@@ankitbansal6 Now i see the second method which is really great and i thank you for that but if someone answer with this method in an interview having emotions is quite good in sql.
Thank you
Hi Ankit thank you for making the video on this question. The way you approach the question makes it easy to understand. Thank you again ❤
select case when s.id = s.id then s.id when t.id = t.id then t.id end as id, case when s.name = s.name then 'new in source' when t.name = t.name then 'new in target' when s.name != t.name then 'mismatch'
end as comment
from source s full outer join target t on s.id = t.id where s.name != t.name or s.name is null or t.name is null
select s.id,
case when s.id in(select id from target) and s.name not in(select name from target) then 'mismatch'
when s.id in(select id from target) then 'source and target'
when s.id not in(select id from target) then 'new in source'
end comment
from source s
where comment!='source and target'
union
select t.id,
case when t.id in(select id from source) and t.name not in(select name from source) then 'mismatch'
when t.id in(select id from source) then 'source and target'
when t.id not in(select id from source) then 'new in target'
end comment
from target t
where comment!='source and target'
Gave a try using your beloved CTE's
with cte1 as (select id,'New in Source' as comment from source a
where a.id not in (select b.id from target b)),
cte2 as (
select a1.id,'New in Target' as comment from target a1
where a1.id not in (select b1.id from source b1)),
cte3 as (select a2.id,'Mismatch in target' as comment from source a2
inner join target b2 on a2.id=b2.id and a2.name!=b2.name)
select * from cte1
union
select * from cte2
union
select * from cte3
Good attempt. Can be simplified with just a full join . Checkout the video.
@@ankitbansal6 sure, will have a look today.
PySpark Version of this problem
ruclips.net/video/JlPG-oIAK2E/видео.htmlsi=KrnNak5FgviIsRO1
with cte2 as(with cte1 as(select *,case when sid is null then 'New in taret' when tid is null then 'New in source' when tname!=sname and tname is not null and sname is not null then 'Mismatch' else 0 end as Comment from
(select target.id as tid,target.name as tname,source.id as sid,source.name as sname from source left
join target on source.id=target.id
union
select target.id as tid,target.name as tname,source.id as sid,source.name as sname from source right
join target on source.id=target.id) a)
select * from cte1 where Comment!='0')
select tid,Comment from cte2 where tid is not null
union all
select sid,Comment from cte2 where tid is null ;