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

[meettoraju]

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

[vijaygupta]

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 sname<>tname then "Missmatch" else "OK" end
as status from cte) as a where status<>"OK"

[rohithr]

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?

[sandhyawishes]

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

[sayedraeespeer]

For how many years of experience was this question relevant?

[blse]

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

[muhammadsalar]

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

[lxckbnj]

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.tname<>s.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

[code_with_logic]

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