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

แชร์
ฝัง
  • เผยแพร่เมื่อ 6 ม.ค. 2025

ความคิดเห็น • 104

  • @ankitbansal6
    @ankitbansal6  ปีที่แล้ว +16

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

  • @Obx_fan4life6
    @Obx_fan4life6 ปีที่แล้ว +4

    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!

  • @Manik65993
    @Manik65993 ปีที่แล้ว +4

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

  • @grim_rreaperr
    @grim_rreaperr ปีที่แล้ว +10

    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;

  • @riya1703
    @riya1703 8 หลายเดือนก่อน +1

    You have earned a new subscriber 🎉

  • @radhikagupta7314
    @radhikagupta7314 9 หลายเดือนก่อน +1

    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

  • @pratik903
    @pratik903 7 หลายเดือนก่อน

    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;

  • @AnupGupta-z9x
    @AnupGupta-z9x 3 หลายเดือนก่อน

    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 ;

  • @jjayeshpawar
    @jjayeshpawar ปีที่แล้ว +9

    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
      @ankitbansal6  ปีที่แล้ว +3

      That will come with practice 🙂

  • @baskarandurai6702
    @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
      @ankitbansal6  ปีที่แล้ว +1

      Thank you. Means a lot to me ❤️

  • @vijaypratap8173
    @vijaypratap8173 ปีที่แล้ว

    You are the best mentor for me👏🙏

  • @d.g0101
    @d.g0101 ปีที่แล้ว

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

  • @avi8016
    @avi8016 ปีที่แล้ว

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

  • @yoyojohnny3776
    @yoyojohnny3776 ปีที่แล้ว

    Today you have earned a new subscriber 🎉

  • @ankushjain4128
    @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.

  • @vinothkumars7421
    @vinothkumars7421 4 หลายเดือนก่อน

    Another intersting one

  • @ritukumari1636
    @ritukumari1636 11 หลายเดือนก่อน

    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.

  • @shraddhajain7753
    @shraddhajain7753 11 หลายเดือนก่อน +3

    (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
      @mayurpatil2984 11 หลายเดือนก่อน

      great....

    • @rr_963
      @rr_963 4 หลายเดือนก่อน

      This solution is incredible. Great work!

    • @sz6618
      @sz6618 3 หลายเดือนก่อน

      its astonishing

  • @dfkgjdflkg
    @dfkgjdflkg ปีที่แล้ว

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

  • @thapelomarakalla
    @thapelomarakalla ปีที่แล้ว +2

    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)

  • @mr.pingpong502
    @mr.pingpong502 6 หลายเดือนก่อน

    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

  • @pmohantymohanty7
    @pmohantymohanty7 9 หลายเดือนก่อน

    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

  • @Vonneuman8676
    @Vonneuman8676 ปีที่แล้ว +1

    Great

  • @swapnam1528
    @swapnam1528 8 หลายเดือนก่อน

    2nd one is awsome

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

  • @PrakashSingh-fh3uf
    @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

  • @rickway2039
    @rickway2039 ปีที่แล้ว

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

  • @PJ-uq6sr
    @PJ-uq6sr 4 หลายเดือนก่อน

    Hi sir, if some one new to Data Analyst role then what level SQL questions, we can expect from the interviewer?

  • @LoveIsLifeTurkishIndia
    @LoveIsLifeTurkishIndia 10 หลายเดือนก่อน

    This will done using case statement?

  • @Its_me_DOt
    @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
      @ankitbansal6  ปีที่แล้ว

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

    • @Its_me_DOt
      @Its_me_DOt ปีที่แล้ว

      @@ankitbansal6 Thank you!🙂 Mentor

  • @nikhilsingh1296
    @nikhilsingh1296 ปีที่แล้ว +2

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

  • @lotus9229
    @lotus9229 11 หลายเดือนก่อน

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

  • @ANKITSINGH-ti9ib
    @ANKITSINGH-ti9ib 9 หลายเดือนก่อน

    Thanks

  • @Ashu23200
    @Ashu23200 7 หลายเดือนก่อน

    some more challenges please.

    • @ankitbansal6
      @ankitbansal6  7 หลายเดือนก่อน +1

      Here you go
      Complex SQL Questions for Interview Preparation: th-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html

  • @pranavipulipaka2243
    @pranavipulipaka2243 9 หลายเดือนก่อน

    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
      @AnuragYadav-jw1dq 9 หลายเดือนก่อน

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

  • @abhishekgarg9029
    @abhishekgarg9029 ปีที่แล้ว +2

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

    • @ankitbansal6
      @ankitbansal6  ปีที่แล้ว +2

      3 plus years

    • @abhishekgarg9029
      @abhishekgarg9029 ปีที่แล้ว +1

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

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

  • @rahulsah207
    @rahulsah207 11 หลายเดือนก่อน

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

  • @supriyasengar5796
    @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!

  • @2412_Sujoy_Das
    @2412_Sujoy_Das ปีที่แล้ว +1

    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
      @ankitbansal6  ปีที่แล้ว

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

    • @2412_Sujoy_Das
      @2412_Sujoy_Das ปีที่แล้ว

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

  • @mayankk1726
    @mayankk1726 ปีที่แล้ว +1

    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
      @ankitbansal6  ปีที่แล้ว

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

  • @hiralalpatra500
    @hiralalpatra500 ปีที่แล้ว +1

    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
      @ankitbansal6  ปีที่แล้ว

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

    • @hiralalpatra500
      @hiralalpatra500 ปีที่แล้ว

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

  • @DeepakSharma-pn8yt
    @DeepakSharma-pn8yt 7 หลายเดือนก่อน

    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'

  • @SanghpalChabukswar
    @SanghpalChabukswar ปีที่แล้ว

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

    • @ankitbansal6
      @ankitbansal6  ปีที่แล้ว

      Use second approach

    • @arjundev4908
      @arjundev4908 11 หลายเดือนก่อน

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

  • @DEwithDhairy
    @DEwithDhairy ปีที่แล้ว +3

    PySpark Approach and Solution explanation video for this problem:
    th-cam.com/video/JlPG-oIAK2E/w-d-xo.htmlsi=BptXwtPBjrHUrhQ4

  • @AmanRaj-uf7wx
    @AmanRaj-uf7wx 10 หลายเดือนก่อน

    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)

  • @ManpreetSingh-tv3rw
    @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
      @ankitbansal6  ปีที่แล้ว

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

    • @ManpreetSingh-tv3rw
      @ManpreetSingh-tv3rw ปีที่แล้ว

      @@ankitbansal6 sure, will have a look today.

  • @parikgrover2281
    @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
      @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
      @reachrishav ปีที่แล้ว +1

      You serious bro? 😂😂

    • @prabhatgupta6415
      @prabhatgupta6415 ปีที่แล้ว +1

      they r not dynamic@@reachrishav

  • @cryofficial2873
    @cryofficial2873 11 หลายเดือนก่อน

    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)

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

  • @florincopaci6821
    @florincopaci6821 ปีที่แล้ว +1

    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
      @ankitbansal6  ปีที่แล้ว +2

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

    • @florincopaci6821
      @florincopaci6821 ปีที่แล้ว

      @@ankitbansal6 I will follow your advice.thank you

    • @florincopaci6821
      @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
      @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 ❤

  • @shraddhajain7753
    @shraddhajain7753 11 หลายเดือนก่อน

    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

  • @thedominatorscircle6762
    @thedominatorscircle6762 ปีที่แล้ว

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

    • @ankitbansal6
      @ankitbansal6  ปีที่แล้ว

      Join , aggregation, case when

  • @nandan7755
    @nandan7755 ปีที่แล้ว

    💯💯❤

  • @saritha3991-o4j
    @saritha3991-o4j ปีที่แล้ว

    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'

  • @deeptisharma9154
    @deeptisharma9154 11 หลายเดือนก่อน

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

    • @rohanjamea2446
      @rohanjamea2446 10 หลายเดือนก่อน +1

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

  • @vishalsonawane.8905
    @vishalsonawane.8905 8 หลายเดือนก่อน

    Done

  • @NayanSrivastava-z5c
    @NayanSrivastava-z5c หลายเดือนก่อน

    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

  • @MonkeyDLuffy4885
    @MonkeyDLuffy4885 4 หลายเดือนก่อน

    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)

  • @thedataguyfromB
    @thedataguyfromB ปีที่แล้ว

    PySpark Version of this problem
    th-cam.com/video/JlPG-oIAK2E/w-d-xo.htmlsi=KrnNak5FgviIsRO1

  • @Pavas-n6j
    @Pavas-n6j 9 หลายเดือนก่อน

    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 ;