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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataanalytics

[ankitbansal]

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;

[radhikagupta]

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

[Manik]

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

[shraddhajain]

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

[AnupGupta-zx]

Thank you for this question sir here is my sql query
select id,
case when count(temp.id) > 1 then 'mismatch'
when id in (select id from source) then 'new in source'
when id in (select id from target) then 'new in target' end as name
from (select * from source
where (id, name) not in (select id, name from target)
union
select * from target
where (id, name) not in (select id, name from source)) temp
group by id ;

[pratik]

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;

[ankushjain]

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.

[Moonlight-]

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!

[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

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

[avi]

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

[d.g]

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

[baskarandurai]

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

[mr.pingpong]

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

[yoyojohnny]

Today you have earned a new subscriber 🎉

[ritukumari]

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.

[dfkgjdflkg]

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

[DEwithDhairy]

PySpark Approach and Solution explanation video for this problem:

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