PWC SQL Interview Question for a Data Analyst Position | SQL For Analytics

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

[@ankitbansal6]

Please do hit the like button on the video for more interview questions.

[@grim_rreaperr]

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;

[@pranaykukadkar7092]

best

[@vijaypratap8173]

You are the best mentor for me👏🙏

[@riya1703]

You have earned a new subscriber 🎉

[@avi8016]

Thanks for bringing your unique perspective while solving the problems!!!

[@ankitbansal6]

My pleasure!

[@Manik65993]

A big fan of your SQL play list.. Really helpful.. Thank you so much..

[@ankitbansal6]

Glad to help 😊

[@Bibble442]

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!

[@Bibble442]

Hi dad ur using my account-

[@ankitbansal6]

Cool, thanks!

[@d.g0101]

Thanks Ankit, solved this in one go. Your vidoes are quite helpful.

[@ankitbansal6]

Most welcome 😊

[@dhyeypatel1335]

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

[@pratik903]

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;

[@thapelomarakalla]

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)

[@Vonneuman8676]

Great

[@yoyojohnny3776]

Today you have earned a new subscriber 🎉

[@ankitbansal6]

Yay! Thank you!

[@shraddhajain7753]

(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)

[@mayurpatil2984]

great....

[@baskarandurai6702]

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

[@ankitbansal6]

Thank you. Means a lot to me ❤️

[@ritukumari1636]

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.

[@radhikagupta7314]

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

[@ankushjain4128]

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.

[@ANKITSINGH-ti9ib]

Thanks

[@jjayeshpawar]

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

[@ankitbansal6]

That will come with practice 🙂

[@swapnam1528]

2nd one is awsome

[@DEwithDhairy]

PySpark Approach and Solution explanation video for this problem: kzbin.info/www/bejne/gJ2zeGClfqZ-aKcsi=BptXwtPBjrHUrhQ4

[@dfkgjdflkg]

I am a fan too, we just don't spend time to express a thank you for all you have tought

[@ankitbansal6]

🙏

[@rickway2039]

I'm curious what the explain plans look like for both these queries.

[@mr.pingpong502]

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

[@PrakashSingh-fh3uf]

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

[@LoveIsLifeTurkishIndia]

This will done using case statement?

[@Ashu23200]

some more challenges please.

[@ankitbansal6]

Here you go Complex SQL Questions for Interview Preparation: kzbin.info/aero/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@mahimkhan134]

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

[@pranavipulipaka2243]

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

[@AnuragYadav-jw1dq]

bhai , kya solution nikala salute . Jo data chahiye wo seggregate krke label lga do .

[@supriyasengar5796]

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!

[@vishalsonawane.8905]

Done

[@cryofficial2873]

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)

[@nandan7755]

💯💯❤

[@Its_me_DOt]

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

[@ankitbansal6]

Practice practice practice. Solve all my questions at least 2 times without checking the solution

[@Its_me_DOt]

@@ankitbansal6 Thank you!🙂 Mentor

[@lotus9229]

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" ??

[@nikhilsingh1296]

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

[@ankitbansal6]

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.

[@nikhilsingh1296]

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

[@2412_Sujoy_Das]

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;

[@ankitbansal6]

You can do simply with full outer join single query. Check my solution.

[@2412_Sujoy_Das]

@@ankitbansal6 Yes sir...I checked....

[@rahulsah207]

Why min(table_name) gives 'target' instead of 'source' if we are seeing it lexicographically

[@jayselokar]

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

[@ashutoshyadav8348]

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

[@AmanRaj-uf7wx]

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)

[@thedominatorscircle6762]

Sir any suggestions for fresher in sql. I completed these topics DDL, DML, DCL, TCL and also join.

[@ankitbansal6]

Join , aggregation, case when

[@mayankk1726]

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 ?

[@ankitbansal6]

Recorded www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

[@abhishekgarg9029]

What experience-level Data Analysts can expect such questions Ankit?

[@ankitbansal6]

3 plus years

[@abhishekgarg9029]

Me being able to solve such problems with 1.25yrs…credits to namaste sql 😉

[@hiralalpatra500]

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)

[@ankitbansal6]

It's really a big solution. You can do it just with a simple full join. Watch the video. Good attempt though 👍

[@hiralalpatra500]

@@ankitbansal6 ok sir❤️ …thank you so much

[@parikgrover2281]

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;

[@parikgrover2281]

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;

[@reachrishav]

You serious bro? 😂😂

[@prabhatgupta6415]

they r not dynamic@@reachrishav

[@DeepakSharma-pn8yt]

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'

[@deeptisharma9154]

The first solution is easy but while using MYSQL full outer join is not available......what can be done in MYSQL?

[@rohanjamea2446]

First left join then union with right join. Hope you figured it out before itself

[@user-kz7hc9qx9j]

how can we execute this query in mysql work bench as full outer join is not working

[@ankitbansal6]

Use second approach

[@arjundev4908]

do left join first and then right join and do a union all. you should see both common and un-common records.

[@shraddhajain7753]

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

[@pmohantymohanty7]

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

[@ManpreetSingh-tv3rw]

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

[@ankitbansal6]

Good attempt. Can be simplified with just a full join . Checkout the video.

[@ManpreetSingh-tv3rw]

@@ankitbansal6 sure, will have a look today.

[@user-xd1tb6fg2d]

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'

[@florincopaci6821]

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

[@ankitbansal6]

That's very long. I think your first click during the interview should be full outer join.

[@florincopaci6821]

@@ankitbansal6 I will follow your advice.thank you

[@florincopaci6821]

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

[@vishwajitkumar3992]

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 ❤

[@thedataguyfromB]

PySpark Version of this problem kzbin.info/www/bejne/gJ2zeGClfqZ-aKcsi=KrnNak5FgviIsRO1

[@user-md1jk3qn3j]

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 ;