SQL Question in AMAZON - DATA Engineer INTERVIEW🔥! Solution EXPLAINED ❤️

[@shashank_mishra]

If you are able to understand the hints for Approach-3 and solve it completely ... I will pin your comment 💯 Also Book Free Trial Now For Job Guaranteed Course - Link in description.

[@jagatkrishna1543]

YES

[@Energybooster111]

Sir please make video on Pivot function in Oracle...

[@datawarrior1482]

recursive cte should have been used coz soln cannot scale to n level easily

[@vinayak6685]

Please see my solution and give feedback as I am new to SQL: select A,B from ( select *,row_number() over (partition by sum order by NULL) as rank from ( select *,(a+b) as sum from number_pairs ) ) where rank = 1

[@anshumansrivastava2801]

Here is my approach, with cte1 as ( select * from number_pairs where b>a) select * from number_pairs np1 where not exists (select 1 from number_pairs np2 where np1.a=np2.b) union select * from cte1; 3rd approach:- select temp.a,temp.b from( select *,row_number() over(partition by (a+b),abs(a-b) order by a,b ) rn from number_pairs)temp where rn=1;

[@balaji8645]

Approach C: The following works for even several duplicates and reverses.. Select a,b from (Select a,b,row_number over (partition by c order by a) as rnum from (Select a,b,case when a

[@rakeshpanigrahi577]

Hi Shashank, please create a youtube playlist for these types of SQL query solving videos, thank you for all the efforts you are putting in order to make us proficient in Data Area.

[@FootballWithAnkit]

Great question. Curious to see other sol in video. My sol is below- select min(A), max(B) from number_pairs group by A+B,abs(A-B) order by min(A) asc ;

[@s.abinash]

Please tell abs

[@madireteja7859]

@@s.abinash it returns absolute value example(1-2=1)instead of -1 it gives 1

[@datawarrior1482]

with c1 as ( select a,b , case when ab then a else b end as scol from number_pairs) select fcol,scol from c1 group by fcol,scol

[@infinity5503]

This will reverse distinct pair as well

[@datawarrior1482]

@@infinity5503 you can flip it again but core idea is i m not using windows function deliberately ;with c1 as ( select a,b , case when ab then a else b end as scol from number_pairs ), c2 as ( select fcol ,scol from c1 group by fcol,scol ) --distinct reverse pair if changed will be null in the join so flip back select case when a is null then c2.scol else c2.fcol end as ffcol ,case when b is null then c2.fcol else c2.scol end as sscol from c2 left join c1 on c1.a=c2.fcol and c1.B=c2.scol

[@gagansingh3481]

It will reverse the order & grouping will get the answer but any other way as using window function with A + b if pair 5,5 and 7,3 and you filter with rownun = 1 then onle of the option 7,3 = 10 , 5+5 = 10 turns the query wrong

[@BismarckWangkhem]

Hi, thanks for sharing :) here is my approach: with cte as (select t.* ,row_number() over (partition by mul order by a) n from (select *, a*b mul from number_pairs)t) select a, b from cte where n = 1;

[@boyinanagendrababu7860]

Select a,b from( select a,b row_number()over(partition by a+b order by 1)rn from table_name ) where rn=1; Dear friends, since here a & b columns are number data types, the above query will help us to get the requested output. Try this one once, thank you

[@ssaaurabh456]

select A,B from( select *, dense_RANK() over (partition by A+b order by A) as rnk from number_pairs ) A where rnk=1

[@makarsh29]

with cte as ( select *, (A+B) as total , row_number() over(partition by (A+B) order by (A+B)) as rn from number_pairs) select A,B from cte where rn=1; easy .................

[@ayushdixit2333]

with CTE AS( SELECT A,B ,row_number() OVER(Partition By A+B,A*B ORDER BY A) as rn FROM number_pairs) Select * from CTE where rn=1;

[@vaibhavverma1340]

why do you used a*b ? we can get the result by using a+b .. please tell me sir is there any specefic reason?

[@its_neeraj_goyal]

@@vaibhavverma1340 a+b & a*b, Using both you get unique solution pair. Example : 4,8.....8,4......2,16.....16,2 all give same product. Similarly, 1+4=3+2=2+3=4+1=5+0=0+5. All give sum 5 . So either one of condition won't work individually

[@b_rizzle4808]

my approach (group and row_number on the partition of the group): with cte1 as( select a, b , case when a>b then a||b else b||a end as grouping from number_pairs) ,cte2 as( select a,b, grouping , row_number() over(partition by grouping order by a) row_num from cte1) select a,b from cte2 where row_num = 1

[@kritikalai8204]

This is my approach.. hope this is what you were expecting: SELECT D.A, D.B FROM (SELECT A, B, ROW_NUMBER() OVER(PARTITION BY (A+B)) row_num FROM numbers) D WHERE D.row_num = 1 OR D.A

[@Quenchedfooty]

Sums and products can vary right. I can express a sum or product in n nunber of pairs. 10 =8+2=6+4=3+7..... You would lose genuine rows.

[@rupamkarmakar2315]

Hi, I did this looks like it also worked - with t1 as (select * from number_pairs n1 where (a,b) in (select b,a from number_pairs n2)), t2 as (select * from number_pairs n1 where (a,b) not in (select b,a from number_pairs n2)) select * from t1 where a

[@venuch3012]

create table #temp(ID1 int, ID2 int) Insert into #temp values( 1,2) ,(3,4) ,(2,5) ,(5,6) ,(4,3) ,(2,1) select t.ID1,t.ID2,tt.id1,tt.id2 from #temp t inner join #temp tt on t.ID1=tt.ID2 where (t.ID2tt.ID1)

[@DilipKumar-of3jk]

Thanks for the video!! Another solution: SELECT A,B FROM ( select A,B, ROW_NUMBER() OVER (PARTITION BY CASE WHEN A

[@KDinesh38]

Third solution using common key and sorting: Select a,b from ( select *,rank() over (order by case when a

[@Reacher1998]

Thanks for the Problem.. Approach 3: select A,B from ( SELECT *, dense_rank() over (partition by a*b order by A) AS prod_rnk FROM PAIRS) a where prod_rnk = 1;

[@chandramurugesan6517]

Nice explanation. Oracle This query works: select t1.* from t1 --non reverse pairs where (t1.A,t1.B) not in ( select t1.A,t1.B from t1 JOIN t1 t2 ON (t1.A = t2.B and t1.B = t2.A) ) UNION select t1.A,t1.B ---- reverse pairs from t1 JOIN t1 t2 ON (t1.A = t2.B and t1.B = t2.A) where t1.A

[@VISHALSINGH-jw2nn]

simple case of self join: with cte as(select *,row_number() over( order by (select null)) as r1 from number_pairs) ,cte1 as(select a.A,a.B,a.r1 as r2 from cte a join cte b on a.A = b.B and a.B=b.A and a.r1

[@omeshagrawal9959]

with helper_table as ( select A,B, concat(A,B) as curr,concat(B,A) as rev,A-B as difference from Temp) select distinct A,B from helper_table where curr not in (select rev from helper_table) or curr in (select curr from helper_table where difference>=0); works with duplicates as well as A and B having same values

[@bharatchaudhury]

Aproach C : with cte as ( Select * , case when A < B then concat(A,B) else concat(B,A) end as pairs from number_pairs ) Select A, B from (Select * , row_number() over(partition by pairs) as rn from cte )X where rn = 1

[@ramchavali5825]

Great explanation on the usage of Exists, select min(a), max(b) from (select a, b, case when a < b then a||'_'||b else b||'_'||a end as concat_ab from number_pairs) t1 group by concat_ab

[@dreams1ist]

My approach: with cte as( select *, lead(A) over (order by B) as next_value from number_pairs) select A, B from cte where B!=next_value or next_value is null;

[@ravisharma3069]

select a,b from number_pairs group by a+b; i tried using window function and got output but then i checked everyone is using window function but not got pinned then again i went through our hint and the best hint was dont think too much complex stick with your basic and you will get you result.

[@skarthikamca]

How about this easy method?? 1. Swapping 2. Distinct 1. Swapping is like Case when aa then b else a end as B 2. apply the distinct on top of this

[@eldragonrv]

The correct answer for approach-3 is using ntile(2). Using row_number() will not give correct answer for those pairs having reversed values in the table where sum will be same like (1,7) (2,6) (3,5). Here's the query : select A,B from ( select *, ntile(2) over (partition by SUMM order by A asc) as RANKS from ( select *, A+B as SUMM from number_pairs ) ) where RANKS == 1

[@akshatjain2315]

with cte as ( select *, row_number() over () as no from table ) select table.A,table.B from table except select x.A,x.B from cte as x join cte as y on x.A=y.B and x.B=y.A and x.no < y.no union select x.A,x.B from cte as x join cte as y on x.A=y.B and x.B=y.A and x.no > y.no

[@KoushikT]

My Approach 3 with cte as ( select A,B,A+B, row_number()over (partition by A+B order by A) as rno from number_pairs ) select * from cte where rno=1

[@PraveenKumar-pd9sx]

Select A, B from number_pairs where (A,B) NOT IN ( SELECT B AS 'A', A AS 'B' FROM number_pairs)

[@saisushanthdurvasulabitsian]

Hi Shashank, thank you so much for sharing such sql questions with us , really helpful, here is my take on the approach-3: i am hashing each a,b combination as 'val1->val2' where val1 = max(a,b) and val2 = min(a,b) and calling it common_key and storing this result into a cte , Ex: 1,2 will have hashed as 2>1 and 2,1 will have hashed as 2->1 and 5,6 will have hashed as 6->5 now using this cte and grouping by common_key, we can eliminate the reverse pairs (as they would hash to same value) by grouping by common key and i am selecting the min(a,b) as a and max(a,b) as b so that only one combination of a,b that hash to same value are picked with cte(a,b,common_key) as (select a , b , case when a < b then concat(b,'->', a) else concat(a,'->', b) end as "common_key" from number_pairs ) select min(a) , max(b) from cte group by common_key; we can also get same result by just selecting a,b instead of min(a),min(b) because using group by without aggregation will act like distinct with cte(a,b,common_key) as (select a , b , case when a < b then concat(b,'->', a) else concat(a,'->', b) end as "common_key" from number_pairs ) select a , b from cte group by common_key; please review and let me know your thoughts , thanks

[@vishwajeetbehera9822]

Select n1.a,n1.b from number_pairs n1 left join number_pairs n2 on n1.a=n2.b group by n1.a,n1.b having count(n1.a) = 1; This is join approach i tried where we are joining with left join so all the pairs having duplicate will have count more than 1 so later we can filter count more than 2

[@MAYANKBHARTI26]

create table number_pairs (A int, B int); insert into number_pairs values (1,2),(3,2),(2,4),(2,1),(5,6),(4,2); select p1.A,p1.B from number_pairs p1 left join number_pairs p2 on p1.B=p2.A and p1.A=p2.B where p1.A

[@janaktyagi2720]

Hi Shashank, my sql solution with demo as (select A,B, case when A>B then Concat(A,B) else Concat(B,A) end as id from number_pairs) , demo2 as (select *,ROW_NUMBER() over (partition by id order by id) as row_count from demo) select A,B from demo2 where row_count= 1

[@lipuleon2796]

just one line select a,b from (select rowid,rank() over (partition by greatest(a,b) , least(a,b) order by a,b) k,a,b from number_pairs) where k=1

[@ananthram8062]

WITH CTE AS ( select *,(case when AB then A ELSE B END) AS B1 from number_pairs ) SELECT A1,B1 FROM CTE GROUP BY A1,B1

[@sathurmukil6308]

WITH tab as ( SELECT greatest(a,b) a,least(a,b) b,row_number() OVER (partition by greatest(a,b),least(a,b) order by greatest(a,b),least(a,b)) rnk from numeric_data ) select a,b from tab where tab.rnk = 1;

[@shaloomathew3628]

with cte as ( select A,B , row_number over(partition by key_value order by A) rnk from (select A,B,A*B as key_value) ) Select A,B from cte where rnk = 1;

[@ranit9841]

Answer to 3rd approach: Select a, b from (Select a, b, row_number() over(partition by ab order by a) as rown from (Select a, b, concat_ws(", ", sort(array(a, b))) as ab from tableName) t) t1 Where rown = 1

[@iamgauravnagar]

Thank you Shashank for great questions and providing detail answer!! Here is my answer for 3rd Approach Select A, B FROM ( Select A,B,row_number() over(partition by id order by id) rn from (Select A,B,A+B as id from number_pairs ) temp1 ) temp Where rn=1 Output A B 1 2 3 2 2 4 5 6

[@infinity5503]

Will it work with pairs likes 3,7 and 5,5 sum is 10

[@krishnachaitanyareddy2781]

@@infinity5503 I got same doubt

[@SudhirKumar-rl4wt]

with temp as ( select a,b,row_number() over(partition by a*b,a+b order by a) rnk from tab1 ) select a,b from temp where rnk=1

[@KisaanTuber]

My solution: with T1 AS (select row_number() over(order by A) as row_id, * from number_pairs), T2 as (select * , case when tab2.row_id > tab1.row_id then 1 when tab2.row_id is null then 1 else 0 end as include_flag from T1 tab1 left join T1 tab2 on tab1.B = tab2.A and tab1.A = tab2.B) select A , B from T2 where include_flag = 1;

[@rakeshchaudhary8255]

My Attempt: with ranked_table as ( select *,dense_rank() over (partition by a*b order by A) rnk from number_pairs) select A,B from ranked_table where rnk=1;

[@VedanttMehtaa]

my Solution: with cte as( select l.A as leftA, l.B as leftB,coalesce(r.A,l.A) as rightA,coalesce(r.B,l.B) as rightB from number_pairs l left join number_pairs r on l.B=r.A ) , f_table as ( select leftA as A, leftB as B from cte where leftA != rightB ), row_num_cte as ( select A,B ,row_number() over(partition by A,B order by A) as rn from f_table ) select A,B from row_num_cte where rn

[@AmitSingh-ut4wt]

WITH CTE AS (SELECT CASE WHEN A>B THEN B ELSE A END A, CASE WHEN B>A THEN B ELSE A END B FROM number_pairs) SELECT DISTINCT A, B FROM CTE;

[@gagansingh3481]

select A1 AS A,b1 AS B FROM ( sELECT * , CASE WHEN A < B THEN A ELSE B END AS A1, CASE WHEN A>B THEN a ELSE b end AS B1 from number_pairs ) AS T1 group by A1,B1

[@harxhdeepsingh]

Approach 4: Add a column named a+b and another named abs(a-b) and and then choose distinct over these two columns

[@dhirajkumarsahu999]

Hi Shashank, thanks for the Video, here is my approach in mysql-- select A, B from number_pairs where (A>B and (B,A) not in (select A, B from number_pairs)) or B>=A;

[@mruthyunjayprasad9578]

select A,B from ( select *,row_number() over( partition by sum order by SUM) row_num from (select*,(A+B) AS SUM from number_pairs ) T2 ) T3 where row_num = 1

[@VinayKumar-st9iq]

Approach -4 : Use Distinct and Case Boom 💥 Select Distinct Case When A

[@shubhamsharma-ne2ke]

WITH src AS ( SELECT CASE WHEN A < B THEN A ELSE B END AS num1, CASE WHEN A > B THEN A ELSE B END AS num2 FROM number_pairs ), final_data AS ( SELECT num1, num2, ROW_NUMBER() OVER (PARTITION BY num1, num2 ORDER BY (SELECT NULL)) AS rn FROM src ) SELECT num1, num2 FROM final_data WHERE rn = 1

[@manishdabas5809]

Select * from (select p.* , row_number() OVER( partition by A+B, case when A>B then A-B else B-A end order by A )rn from number_pairs p)Z where Z.rn=1

[@AkshayDabhade-q6r]

@E-learning Bridge with tb1 as (select A,B,ROW_NUMBER() over(partition by A+B order by A) as rn2 from number_pairs) select A,B from tb1 where rn2=1 This query is also working perfectly fine, is this a right approach ?

[@saikatde6343]

Hi Shashank , can you please check the solution? with cte as ( select * , case when B>A then A else B end as p1, case when B

[@artigupta4300]

Using union we can have this output. Just : select a, b from t1 union select b, a from t2:

[@shashankemani1609]

looks simple yet complex to solve. Thanks for explaining!

[@ajaydhanwani4571]

Even I got similar question in interview, instead of numbers i got location like if pune to kolkata exists then eliminate kolkata to pune

[@rajdeepchakraborty7031]

select A, B from number_pairs where (B-A) != -1 and -2 it also give result

[@sachurv7822]

Damn genius. yet simple still mastermind

[@gagansingh3481]

It remove 3,2 pair which is needed wrong query

[@rajdeepchakraborty7031]

@@gagansingh3481 it's written random over there

[@albinchandy6056]

select A,B from number_pairs t1 where (A,B) not in (select B,A from number_pairs t2 where t1.A>t2.A) ;

[@porshiabanik9361]

Here is my approach: WITH REVERSED_PAIRS AS (SELECT A,B, ROW_NUMBER()OVER(ORDER BY A) AS ROW_NUM FROM number_pairs WHERE A>B), ORDERED_PAIRS AS (SELECT A,B FROM number_pairs WHERE A

[@abhisheksaxena2211]

Thanks for sharing your knowledge with us :) Here's my solution with approach 3 with cte as ( select a.number1,a.number2, row_number()over (partition by number1+number2 order by number1) as rownumber from #test a ) select number1,number2 from cte where rownumber=1

[@himanshujain2047]

This will create single partition for all records which will give the same sum like- 1,4 and 2,3 will give 5 and this query will remove 2,3 from the result set and thus will remove valid records as well.

[@abhisheksaxena2211]

@@himanshujain2047 Thanks for your reply. I try to update solution

[@abhisheksaxena2211]

Here is the updated solution: For Approach 3: with cte as ( select a.number1,a.number2, row_number()over (partition by number1+number2,number1*number2 order by number1) as rownumber from #test a ) select number1,number2 from cte where rownumber=1

[@rajeshawasthi3727]

SELECT A,B FROM ( SELECT A,B,ROW_NUMBER() OVER (PARTITION BY A+B ORDER BY A+B) Rnk FROM number_pairs )RS WHERE RS.Rnk=1

[@infinity5503]

Solution using partition : select a,b from (select a,b,row_number() over (partition by min(a,b),max(a,b)) rn from numbers ) where rn = 1; min(a,b),max(a,b) - will serve as key Like for pair 1,2 and 2,1 Key = 1,2 Shashank sir plz post more questions like this it helps a lot

[@otmanmssaddar5885]

very new to sql, for the 3rd approach we coukd sort using DISTINCT operator. SELECT DISTINCT(A,B) FROM table; What do you think ?

[@SANJAYYADAV-hm2bs]

please upload more such(good level) interview question related to product based companies.

[@RitikaSingh-in6dt]

Here is my solution: With number_pairs1 As( Select A,B, CASE WHEN A>B THEN B ELSE A END AS A1, CASE WHEN A>B THEN A ELSE B END AS B1 FROM number_pairs) SELECT T.A, T.B FROM ( SELECT A,B, ROW_NUMBER() OVER(PARTITION BY A1,B1 ORDER BY A ASC) AS ROW FROM number_pairs1) AS T WHERE T.ROW=1; Open for recommendations.

[@rishav144]

need more such SQL videos

[@rockstar-sg2tz]

Tried using not in : SELECT A,B from newList where (A,B) NOT IN(SELECT B,A FROM newList where B>A);

[@rohitsatpute2209]

Bhai please data analyst and scientist ke interview questions ka video bnao na for fresher, entry-level, mid senior level ke liye

[@Project-e5u]

Select a,b From number_pairs group by (a+b)

[@rockykefunday2707]

select * from table minus select * from table where a>b

[@debabratabar2008]

hi shashank , i have tried one new approach , can you please check query ---> select id1,id2 from (select id1,id2 , (id1+id2 ) as total from table1 ) as table2 group by table2.total i have taken two column id1 and id2 instead A,B please tell me , is this solution correct ?

[@dishitaneve8362]

I saw your channel focuses on data field. Please make more video for data science. Freshers

[@rose9466]

Hy Shashank, please make more of SQL videos and also if you could make Python questions too

[@mihirbhagat54]

Hi Shashank, I have a question in mind, what if the value of A, B are same then how will the greater than condition work?? Please, can u elaborate the condition for that situation? I don't know if it is possible or not.

[@chaitanyapanchal1312]

Hi Shashank, My Approach is: If we have to find the pair value of A and B having 2,1 or 1,2. Then as we can see if we have required pair then sum of A+B or B+A will be equal. Therefore we can right require below: select A,B,A+B as total from number_pairs group by total having count(total)>1 Please Correct me if I am wrong.

[@strong8515]

Good work, this is working.

[@parveen8122]

@chaitanya Panchal if we have 5,2 and 2,5 and 6,1. sum of all three is same but not all are reverse. hence add a+b = b+a and a-b = b-a

[@aayushsingla3034]

Can you please make more videos or a playlist like this in which you will provide good questions along with their solutions for placement in the good company. Thank you!

[@AftabAlam-lh1fe]

I am MBA non IT background, having 13 years experience. Is it possible to become data engineer, and is it good idea for me? Pls guide

[@yourajbadgujar7837]

sir mysql aur sql server me kyon sa job ke liya acha hoga?

[@shahinurrahman7745]

Thanks for these amazing videos Shashank. One request, can you please make a video on system design for DE? Like what are the main topics asked in DE system design interviews at mid levels (7-8 YOE)

[@shashank_mishra]

Sure Noted

[@realcontent2008]

3 approach all about partition by and row number

[@sandeepanpaul2653]

It would be great if you could put up a drive link of the most asked interview questions for DE. Amazing content as always, thanks

[@rakeshd7131]

select * from table_name where (c1,c2) in (select c2,c1 from table_name);

[@rakeshd7131]

a little correction,add 'not in; instead of 'in'

[@infinity5503]

We need one pair but this will eliminate both pair

[@Qwirk-Yt]

Sir Btech from tier 3 college or BCA + MCA from good college which is best one please reply Or make video on this

[@jaikishankumar3377]

If the data looks like: A B 1 1 2 2 1 1 2 2 Then, the same approach and DISTINCT or GROUP BY can be added

[@VamsiKrishna-je3iy]

Please more of aggregate functions in sql using group by having partition by for department wise problems it's getting worse for me

[@VijayVijay-pn3cp]

Hey shashank,,please clear me out from this doubt. I know I feel this question is not Related to you. But I value your Suggestion I'm done diploma in mechanical engineering. & working as a designer engineer (catia v5) in Automotive Industry. Can you suggest me to level up my career or any other best job opportunities to get around 8LPA..

[@kikikiki4588]

Please create playlists for SQL videos so that we can easily access them.

[@kikikiki4588]

Thank you

[@liveeatwork3601]

Do we have any platform for data professionals similar to crio which provides learning opportunities and job opportunities for good companies?

[@Vikrammaity]

hi shashank... looks like approach 1 will fail for record with (4,4) (5,5) (6,6)

[@shashank_mishra]

Yes I discussed about this point in Problem Statement Timestamp

[@jagatkrishna1543]

Thanks Sir 🙏💕

[@RAHULRAJ-xh5fy]

NEED HELP PLEASE....plz guide Sir, I am getting project in Oracle PL/SQL,Linux Shell scripting will it help in future while switching to data science field.my core ares of interest in big data and data science, i don't know what to do if I refuse the project they will put in bench

[@ravindra2141]

from personal experience , yes . oracle and pl/sql will help you and shell scripting is always helpful . however they will give you strong roots but you wont get into big data or data science or cloud with them . you can pursue courses in parallel according to your interest and apply for roles .

[@RAHULRAJ-xh5fy]

@@ravindra2141 thanku so much bhaiya

[@tanushreesaha2276]

SELECT * FROM NUMBER_PAIRS WHERE A IN(1,3,4,5) and B in(2,2,6,2); 😅😅😅😅😅😅

[@dishitaneve8362]

Please make more such video

[@mohammedghouse9088]

Can we use UNION operator?

[@jatinnandwani6678]

Thanks so much

[@AbhishekKumar-bh3lx]

We can also create key based on sum and product of A and B.

[@infinity5503]

Will it work if we have pair like 3,7 and 5,5

[@AbhishekKumar-bh3lx]

@@infinity5503 Yes right. 3,7 = 10:21 5,5 = 10:25 key of sum & product should still remain distinct.

[@infinity5503]

@@AbhishekKumar-bh3lx you can use partition by min(a,b), max(a,b).

[@harshkulkarni7729]

SELECT * FROM number_pairs GROUP BY a+b,a*b

[@sandipansarkar9211]

finished watching

[@akshaybaura]

select a as col1, b as col2 from number_pairs union select b as col1, a as col2 from number_pairs

[@jeekakrishna]

nice