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!
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;
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
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;
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 ;
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
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.
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.
(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)
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)
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
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
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
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
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
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
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
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
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!
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;
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 ?
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)
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'
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)
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
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;
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)
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
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
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 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
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'
select id , CASE WHEN id not in(select id from target) THEN 'New in Source' END 'Comments' from source where id not in(select id from target) union select id , CASE WHEN id not in(select id from source) THEN 'New in Target' END 'Comments' from target where id not in(select id from source) union select source.id , CASE WHEN source.id = target.id and source.name!=target.name THEN 'Mismatch' END 'Comments' from source join target on source.id = target.id where source.id = target.id and source.name!=target.name my approach
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 ALL 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 ALL SELECT id, 'Mismatch' AS Comment FROM source WHERE (id IN (SELECT id FROM source) AND id IN (SELECT id FROM target)) AND name NOT IN (SELECT name FROM target)
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 ;
Please do hit the like button on the video for more interview questions.
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!
A big fan of your SQL play list.. Really helpful.. Thank you so much..
Glad to help 😊
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 have earned a new subscriber 🎉
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
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;
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 ;
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 🙂
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 ❤️
You are the best mentor for me👏🙏
Thanks Ankit, solved this in one go. Your vidoes are quite helpful.
Most welcome 😊
Thanks for bringing your unique perspective while solving the problems!!!
My pleasure!
Today you have earned a new subscriber 🎉
Yay! Thank you!
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.
Another intersting one
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.
(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....
This solution is incredible. Great work!
its astonishing
I am a fan too, we just don't spend time to express a thank you for all you have tought
🙏
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)
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
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
Great
2nd one is awsome
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
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
I'm curious what the explain plans look like for both these queries.
Hi sir, if some one new to Data Analyst role then what level SQL questions, we can expect from the interviewer?
This will done using case statement?
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 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
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" ??
Thanks
some more challenges please.
Here you go
Complex SQL Questions for Interview Preparation: th-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
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 .
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_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
Why min(table_name) gives 'target' instead of 'source' if we are seeing it lexicographically
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!
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....
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
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
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'
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.
PySpark Approach and Solution explanation video for this problem:
th-cam.com/video/JlPG-oIAK2E/w-d-xo.htmlsi=BptXwtPBjrHUrhQ4
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)
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.
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
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)
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
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
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
Sir any suggestions for fresher in sql.
I completed these topics DDL, DML, DCL, TCL and also join.
Join , aggregation, case when
💯💯❤
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'
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
Done
select id , CASE WHEN id not in(select id from target) THEN 'New in Source' END 'Comments'
from source
where id not in(select id from target)
union
select id , CASE WHEN id not in(select id from source) THEN 'New in Target' END 'Comments'
from target
where id not in(select id from source)
union
select source.id ,
CASE WHEN source.id = target.id and source.name!=target.name THEN 'Mismatch' END 'Comments'
from source join target on source.id = target.id
where
source.id = target.id and source.name!=target.name
my approach
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 ALL
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 ALL
SELECT id, 'Mismatch' AS Comment
FROM source
WHERE (id IN (SELECT id FROM source) AND id IN (SELECT id FROM target)) AND name NOT IN (SELECT name FROM target)
PySpark Version of this problem
th-cam.com/video/JlPG-oIAK2E/w-d-xo.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 ;