PWC Data Analyst Interview | SQL Intermediate Question 11

Question - Give review for the ids in the tables based on their occurrences in the tables.
DDL Commands -
drop table sources;
drop table targets
create table sources(
sid int,
sname varchar(50)
);
create table targets(
tid int,
tname varchar(50)
);
insert into sources values(1,'A'),(2,'B'),(3,'C'),(4,'D');
insert into targets values(1,'A'),(2,'B'),(4,'X'),(5,'F');
select * from sources;
select * from targets;
#placement #placements #reels #dataanalytics #ml #facebook #instagram #interviewquestions #sql #google #amazon #flipkart #ml #ai #dataengineering

[@user-lx1ck9bn2j]

with cte as
(select s.sid,'new in source' as comment
from sources s left join targets t
on s.sid=t.tid where t.tid is null
union all
select t.tid,'Mismatch' as comment from targets t
join sources s on t.tid=s.sid where t.tnames.sname
union all
select t.tid,'new in Target' as comment from targets t
left join sources s on t.tid=s.sid where s.sid is null
)
select * from cte

[@sandhyawishes]

I see this approach is more easy than @ankit bansal. Your way of teaching is also good. Subscribed

[@Code-Con]

Thanks 👍🏾

[@blse2000]

WITH M
AS
(SELECT S.*,T.*
FROM SOURCES S
FULL OUTER JOIN TARGETS T
ON sid = tid
)
SELECT CASE WHEN tid is null then concat(sid,' new in Source')
WHEN sid is null then concat(tid,' new in Target')
WHEN sname tname then 'Mismatched'
else '' END AS result
FROM M
WHERE tid is null OR sid is null OR sname tname

[@sayedraeespeer]

For how many years of experience was this question relevant?

[@muhammadsalar8905]

I've just started watching the video, I'm hoping the approach to the problem is different from @ankit bansal

[@Code-Con]

May be, haven't watched sir's approach till now.

[@muhammadsalar8905]

@@Code-Con You haven't copied ankit bansal, so kudos man, good job & keep making videos like this consistently, subscribed!

[@meettoraju]

Hi bro,thanks for sharing this.Please focus on youtube Title,description,thumbnail also..then only will get more views and subscribers.

[@code_with_logic449]

copy past question and solution,
why you do like this
do some real good question

[@rohithr9122]

select * from(
select sid , case when sname tname then 'Mimatched'
when tid is null then 'New in sources' end Review
from sources as s
left join
targets t
on s.sid = t.tid) t1
where Review is not null
union
select tid , 'New in targets' from targets
where tid not in(
select sid from sources)
is this correct solution?

[@vijaygupta7059]

same solution as per videos tries in MYSQL
select coalesce(sid, tid ) as id , status from
(with cte as
(Select * from sources left join targets on sources.sid = targets.tid union all
Select * from sources right join targets on sources.sid = targets.tid where sid is null )
Select sid, tid
,case when sid is not null and tid is null then "New in Sourse"
when sid is null and tid is not null then "New in Target"
when sid=tid and snametname then "Missmatch" else "OK" end
as status from cte) as a where status"OK"

[@Code-Con]

Nice one 👏

[@poushalinag7942]

My SQL doesn't allow full joins 🙁

[@vijaygupta7059]

@@poushalinag7942 is MYSQL we can do using other method like below its same way work as full join
Select * from sources left join targets on sources.sid = targets.tid
union all
Select * from sources right join targets on sources.sid = targets.tid
where sid is null

[@anirbanbiswas7624]

@@poushalinag7942 Yes,same issue here

[@user-lx1ck9bn2j]

easy way :)
with cte as
(select s.sid,'new in source' as comment
from sources s left join targets t
on s.sid=t.tid where t.tid is null
union all
select t.tid,'Mismatch' as comment from targets t
join sources s on t.tid=s.sid where t.tnames.sname
union all
select t.tid,'new in Target' as comment from targets t
left join sources s on t.tid=s.sid where s.sid is null
)
select * from cte

[@raagpathy]

Very nice

[@Hkumar_new]

Hindi bolo sir.. smjh nhi aata

[@Code-Con]

Ok will record in hindi too