Remove Redundant Pairs - SQL Interview Query 1

No video

Remove Redundant Pairs - SQL Interview Query 1 | SQL Problem Level "HARD"

Рет қаралды 98,288

Күн бұрын

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the first video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. In these videos, I will explain how to solve and address such SQL queries during interviews.
This particular video covers an SQL Interview query that I found on Reddit.
Learn and Practice SQL on LearnSQL platform below:
learnsql.com/?...
Let's follow the below routine to make the best use of it:
1. Watch the KZfaq video (first half) to understand the problem statement.
2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.
3. Share your solution on Discord and discuss different solutions and issues on my Discord server.
4. Watch the second half of my KZfaq video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/bl...
Timeline:
00:00 Intro about the #30DaySQLQueryChallenge
00:58 Problem Statement Query1
05:43 #30DaySQLQueryChallenge Process to be followed
07:16 Solution to the Query1
Thanks for participating in this challenge!
Good luck and Happy Learning!

Пікірлер: 194

@malcorub 5 ай бұрын

I love the idea! I will be tuning in every day this month.... even on weekends with hangover. LOL

@techTFQ 5 ай бұрын

Thats the spirit 😃

@somnathdutta6311 5 ай бұрын

@@techTFQ Don't give the ans in same video. give ans in next video. It will be more helpful. It will also create a hook for your next video and a short of competition as well.

@umangbhatnagar1415 4 ай бұрын

@@somnathdutta6311 Why making things tough. Let it be easy. We are not as good as you. The video's purpose is to help community who is still in its very crude form.

@Useruse867 2 ай бұрын

@@somnathdutta6311 no one will ever watch as there are many channels who solve and give answer there.

@anirbanbiswas7624 7 күн бұрын

with cte as(select *,row_number() over() as rn from brands), cte2 as( select *,case when custom1=custom3 and custom2=custom4 then 1 else 0 end as flag1, case when custom1custom3 and custom2custom4 or brand2 is null then 1 else 0 end as flag2 from cte ) select brand1,brand2,year,custom1,custom2,custom3,custom4 from cte2 where rn=1 or rn>2

@mindlessscroll 4 ай бұрын

With rm as (select *, IF(STRCMP(brand1,brand2) > 0, concat(brand1,brand2,year), concat (brand2,brand1,year)) as con from brands ), rm1 as( select *, row_number() OVER(PARTITION BY con order by con ) as id from rm) select brand1, brand2, year from rm1 where id=1 or (custom1 custom3 and custom2 custom4)

@gauravtanwar8886 2 ай бұрын

as per the 3rd condition you need a little adjustment in your code (custom1 custom3 OR custom2 custom4)*

@gauravtanwar8886 2 ай бұрын

but thanks to your answer i got to know about STRCMP

@VijayKumar-ec3dz 4 ай бұрын

Hi sir, My solution is kind of similar as below with cte as (select *,case when custom1=custom3 and custom2=custom4 then 1 when custom1custom3 or custom2custom4 then 2 else 3 end as r from t1) select * except(r,rn) from (select *,row_number() over(partition by r,year order by year) as rn from cte) a where coalesce(case when r=1 then rn=1 else null end,1=1)

@chahatjain3745 5 ай бұрын

with cte as (Select *, row_number() over(partition by Case when brand1>brand2 then CONCAT(brand1,brand2,year) else CONCAT(brand2,brand1,year) end order by year) as rn from brands) Select * from cte rn where rn=1 or (custom1 custom3 and custom2 custom4)

@ContentRoomB Ай бұрын

I'm finally done with query 1 with your solution help. looking forward to cracking future questions on my own

@12Patni 3 ай бұрын

Thoufiq Mohammed Thank you for the thorough explanation. The problem's difficulty level is high. Below is my solution: I utilized others columns in the Partition By clause, ensuring accurate row numbering without the need for an additional where clause condition. ;with CTE AS ( Select * , case when brand1 < brand2 then CONCAT(brand1,brand2,year) else CONCAT(brand2,brand1,year) end as Brand from [Asif_SQL].[dbo].[brands] ), CTE_RN AS ( SELECT * , ROW_NUMBER () OVER (PARTITION BY BRAND,CUSTOM1,CUSTOM2,CUSTOM3,CUSTOM4 ORDER BY BRAND ASC ) AS RN FROM CTE ) SELECT brand1,brand2,year,custom1,custom2,custom3,custom4 FROM CTE_RN WHERE RN = 1

@shabbiransari9731 2 ай бұрын

What will you do if the brand is of equal length?

@ganeshsundarachary3239 5 ай бұрын

Awesome , Great learning Experience. Pls make more such challenges.

@inderpanda1 5 ай бұрын

In my opinion at least out of 30 question first 5 should have been Easy to Medium Level. It seems to be hard and complex for me. The questions should have been from only 1 function among these CTE, Case, Subquery, etc

@amlansasmal 5 ай бұрын

same brother:)

@dataengineeringsimplified1049 2 ай бұрын

with tbl as ( SELECT *,case when brand1

@37_om 8 күн бұрын

This is somewhat more clear i think

@splendidabhi 5 ай бұрын

Excited to kick off the #30DaySQLQueryChallenge with Query #1! Let's sharpen those SQL skills together and ace those interview queries. Can't wait to see what we'll learn throughout this challenge! 💻💡

@techTFQ 5 ай бұрын

Amazing 😍

@rahuldey4730 5 ай бұрын

Loved the question Sir. Even if I was not able to solve this at my own but after I saw the solution and the logic behind this I am speechless and amazed. This is the type of content we need.

@The-Patil 9 күн бұрын

I believe below query will give the same result. Select * from brands where custom1=custom3 and custom2=custom4 fetch first 1 row UNION select * from brands where custom1 != custom3 or custom2 != custom4 UNION Select * from brands where custom3 is null or custom4 is null

@junaidmahmud2894 5 ай бұрын

Great solution. I'm afraid I am a bit late to start but I will definitely continue this!

@vishalmaurya3008 5 ай бұрын

❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅 Thank you sir that is extremely amazing❤❤❤❤❤❤❤

@kalpeshispatil 5 ай бұрын

You are simply amazing buddy... I am user CTE many times, but not as you shown.. Fan of yours... ❤❤ Will be tuning daily.. Your first example has cracked and shown us where we land in DB queries... Thank You For Us Efforts

@beautifulworld3976 5 ай бұрын

It is informative from the problem statement we get idea how to solve thank you so much it is helpful for me this 30 days i will become still more strong in SQL database

@SivaKumarP_alliswell 5 ай бұрын

Below query will also work right ? with tab1 as (select * from brands where c1 c3 or c2 c4), tab2 as (select * from brands where (b1 is null or b2 is null), tab3 as (select b1, b2, year, c1, c2, c3, c4 from brands where c1 = c3 and c2 = c4), tab4 as (select b2, b1, year, c1, c2, c3, c4 from brands where c1 = c3 and c2 = c4) select * from tab1 UNION select * from tab2 UNION select * from tab3 UNION select * from tab4;

@ritika29 4 ай бұрын

Thanks, it was Helpful 👍 Quick question, what if there was a row with NULL in brand1 column Example: INSERT INTO brands VALUES ( NULL,'lava', 2020, 5, 9, NULL, NULL);

@user-ox4fi3ls8e 4 ай бұрын

we can put union as well in place of or, and case statements for the conditions in place of Row_num window function, rest is same.

@ManishChauhan-fb7uz 5 ай бұрын

with cte as ( select * , row_number() over(partition by id order by id) as rn from ( SELECT * , case when brand1 > brand2 then concat(brand1, brand2, year) else concat(brand2, brand1, year) end as id , case when custom1 = custom3 or custom2 = custom4 then 1 else 0 end as flag FROM brands ) as t ) select * from cte where rn = 1 or (rn > 1 and flag = 0)

@Dopamine.Moments 5 ай бұрын

You are helping me a lot to build my understanding about SQL, Thanks to you I have cleared most of the interviews by watching and following you. I will be turning in for this series. 😊

@pavanigoud98 5 ай бұрын

Please also doo the sql interview questions for data analyst. Lots of love ❤

@aarizaziz4380 5 ай бұрын

Thank you very very very much for this initiative. Looking forward to a multi decade event by you just like this! You are one of the best instructors I have come across internet for SQL. hats off to you for taking time to educate us and building the community.

@MwAVlogs 5 ай бұрын

I am new in this field, but like the way you explain. It is a complex one I know but still after practicing I’ll get it.

@khushboobaghel6594 5 ай бұрын

Thankyou for guiding for 30 days it will help us to improove Sql.

@vamshijiguru7636 Ай бұрын

select brand1,brand2,year,custom1,custom2,custom3,custom4 from ( select *, row_number ()over (partition by chg,custom1,custom3) rn From (select* , coalesce(case when custom1 = custom3 and custom2 = custom4 then 1 when custom1 != custom3 OR custom2 != custom4 then 0 end,0) chg from brands)a)a where rn =1

@CricketLivejavwad 5 ай бұрын

Techtfq is back💯😎 We all are excited.

@poojahr5670 5 ай бұрын

My Solution with Cte as (Select *, Row_Number() over () as id ,case when Custom1=Custom3 and custom2=custom4 then 1 else 0 end as Type from brands) Select T1.brand1,T1.brand2,T1.year,T1.custom1,T1.custom2,T1.custom3,T1.custom4 from Cte T1 join Cte T2 on T1.brand1 =T2.brand2 Where T1.id

@haleylearn 3 ай бұрын

For condition: For pairs of brands in the same year ---> if custom1 != custom3 OR custom2 != custom4 : then keep both pairs Why is it (custom1 custom3 AND custom2 custom4) NOT is (custom1 custom3 OR custom2 custom4) ?

@piyushnautiyal6271 5 ай бұрын

amazing que and the way of solving is awesome.. thank u so much techTFQ!!

@shwetamishra1497 5 ай бұрын

Very helpful . Thank you so much big bro for this series, just keep continue.🙏

@user-vo3yr8hh8w 5 ай бұрын

with cte_check as ( select *, case when (custom1custom3 and custom2custom4) or custom1 is null or custom2 is null or custom3 is null or custom4 is null then 1 when brand1 = LEAD(brand2) over(order by year) and LEAD(brand1) over(order by year)=brand2 and year = LEAD(year) over(order by year) and (custom1=custom3 and custom2=custom4) then 2 end as dd from brands ) select brand1,brand2,year,custom1,custom2,custom3,custom4 from cte_check where dd in (1,2)

@HimanshuSingh-cf7wr 5 ай бұрын

My Approach:- select BRAND1,BRAND2,year,Custom1,Custom2,Custom3,Custom4 from (select case when rk=2 and Custom1==custom3 and custom2==custom4 then 1 else 0 end as duplicate_entry,p.* from (select row_number() over (partition by common_name,year order by Brand1) as rk,k.* from (select *,concat(greatest(BRAND1,BRAND2),least(BRAND1,BRAND2)) as common_name from input_table)k)p)o where duplicate_entry1 order by BRAND1

@pinkfloyd2642 5 ай бұрын

Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.

@sabmelegarebaba1233 5 ай бұрын

Bro pz upload 2 video per day 😊

@sweetysweetyvghb Ай бұрын

I couldn't solve myself, but I find it easy after seeing the solution..not sure y.

@sriharidhanakshirur9245 5 ай бұрын

Intresting Question! Im sure if anyone deligently follow this #30DaysSQLChallenge his/her SQL skills drastically improves

@manirahofred801 4 ай бұрын

This was really helpful. However, if the dataset was too big and brand records were many & different, I don't think "case when" functionality would be helpful. At the moment because of the dataset we have, that would be the easiest way!

@yi-lehung6311 5 ай бұрын

still the best SQL video!!!! I love this idea, please continue!

@padhenamit 5 ай бұрын

Thank you for your amazing lectures and feedbacks and thank you for being so supportive!

@khushbu3025 5 ай бұрын

Really, It's very nice 🙂 Thank you for this series.

@Hsalz 5 ай бұрын

Simply amazing. Thanks, Thoufiq!

@haleylearn 3 ай бұрын

SELECT DISTINCT LEAST(brand1, brand2) AS brand1, GREATEST(brand1, brand2) AS brand2, custom1, custom2, custom3, custom4, year FROM brands

@andreanlobo7373 5 ай бұрын

extremely insightful.. thanks

@HarshaV44 5 ай бұрын

Thank you so much. little modified : WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY CASE WHEN brand1 < brand2 THEN CONCAT (brand1,brand2,year) ELSE CONCAT (brand2,brand1,year) END ORDER BY (SELECT NULL)) as RKNO FROM brands ) SELECT * FROM CTE WHERE RKNO = 1 or (custom1 custom3 and custom2 custom4)

@zeeshanahmed2594 5 ай бұрын

Great modification! I think in where clause it should be OR in place of AND.

@Venkatesh-bs7kb 5 ай бұрын

Thanks a lot for the video. the way you explain CTE's are awesome!! in your solution line number 20, as per problem statement, it should be an OR condition ryt ? ('or (custom1 custom3 OR custom2 custom4)')

@user-js2vt3nz2k Ай бұрын

Thank you for explaining thoroughly :-)

@prithvirajpatil7388 5 ай бұрын

Good question❤ starting it from today.

@fathimafarahna2633 5 ай бұрын

Amazing 🔥🎉

@techTFQ 5 ай бұрын

Glad you like it!

@ramakrishnatirumala428 4 ай бұрын

super videos bro...keep posting

@sourabroy7787 5 ай бұрын

great initiative and quality content 👍

@mkgeidam 5 ай бұрын

Thanks you Sir, it really helpful

@sandeepkumarsingh7981 5 ай бұрын

Hi Tafiq, I'm also your huge friend, and your technic is always help to improve my skill. I saw the solution and the logic behind this I am speechless and amazed. This is the type of content we need. thanks you so much. Hey Tofiq, I have downloaded you content but that file is blank. please help me on that.....

@VivekKBangaru 4 ай бұрын

Awesome TFQ

@mohdtoufique3786 5 ай бұрын

Thanks a lot for the content.. WITH PAIRS AS( SELECT *,CASE WHEN brand1

@abhishekkukreja6735 3 ай бұрын

Awesome, Can you share excel page : it's good to store the results and everthing in excel

@livelovelaugh4050 5 ай бұрын

Thank you so much for starting this series. 🙏

@harinadhbabu309 7 күн бұрын

hi taufiq, Your videos are helping me alot, but your blog has not been opening for last few days. Please do needfull

@pavankumar-cw2sx 5 ай бұрын

@techTFQ Can we give OR condition instead of AND in WHERE condition at final query like as: where rn = 1 or (custom1 custom3 OR custom2 custom4); ????????????????

@johnsonrajendran6194 5 ай бұрын

Love the way you teach ❤

@koushikdey100 4 ай бұрын

Great !!, Thank you

@vikrantks 5 ай бұрын

Another approach (solved in oracle db): SELECT rownum, B1.* FROM BRANDS B1, BRANDS B2 WHERE B1.BRAND1||B1.YEAR = B2.BRAND2||B2.YEAR AND B1.CUSTOM1 = B1.CUSTOM3 AND B1.CUSTOM2 = B1.CUSTOM4 and rownum = 1 UNION SELECT rownum,B1.* FROM BRANDS B1, BRANDS B2 WHERE B1.BRAND1||B1.YEAR = B2.BRAND2||B2.YEAR AND (B1.CUSTOM1 != B1.CUSTOM3 OR B1.CUSTOM2 != B1.CUSTOM4) UNION select rownum,b.* from brands b minus ( SELECT rownum,B1.* FROM BRANDS B1, BRANDS B2 WHERE B1.BRAND1||B1.YEAR = B2.BRAND2||B2.YEAR );

@Itsourfamilys 3 ай бұрын

Thank you so much for SQL videos

@karangupta_DE 5 ай бұрын

with cte as ( select *, case when custom1 = custom3 and custom2 = custom4 then 'match' else 'not_matched' end as is_match, row_number()over(order by (select null)) as rn, row_number()over(partition by year order by (select null)) as rn_1 from brands ), cte1 as ( select *, (rn - rn_1) as pair from cte ) select min(brand1) as brand1, max(brand2) as brand2, max(year) as year, max(custom1) as custom1, max(custom2) as custom2, max(custom3) as custom3, max(custom4) as custom4 from cte1 where is_match = 'match' group by pair union all select brand1, brand2, year, custom1, custom2, custom3, custom4 from cte1 where true and is_match != 'match';

@nointernet...1800 5 ай бұрын

Nice video

@vishisbest 5 ай бұрын

This was pretty interesting

@riyatiwari4767 5 ай бұрын

Can you please make video on how to write dynamic query in pyspark. That would be a lot of help. Thank u❤

@sammail96 5 ай бұрын

SELECT BRAND1, BRAND2, YEAR, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4 FROM ( SELECT P.*, ROW_NUMBER() OVER (PARTITION BY YEAR, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4 ORDER BY YEAR) AS RK FROM PHONE P ) WHERE RK = 1 ORDER BY CUSTOM1;

@navaneeth6414 5 ай бұрын

Great! video Thanks

@techTFQ 5 ай бұрын

Glad you liked it!

@pathanfirozkhan5503 5 ай бұрын

Amazing video sir, Thanks for your time

@sandydsa 5 ай бұрын

Fantastic 🌻🌻🌻

@MuhammedSavadkv 3 ай бұрын

Great. Thank you

@jjayeshpawar 5 ай бұрын

with cte as (select *, case when brand1 < brand2 then brand1||''||brand2||''|| year else brand2||''||brand1||''|| year end as pair from brands) select brand1,brand2,year,custom1,custom2,custom3,custom4 from ( select *, case when custom1 = custom3 then row_number() over(partition by pair) else dense_rank() over(partition by pair) end as rkk from cte) where rkk = 1

@saitejamanchi1389 5 ай бұрын

I think you should also add custom2 = custom4 in the condition in the case clause but very cool idea though!

@nadimatlanagesh1951 5 ай бұрын

My approach: SELECT brand1,brand2,YEAR,custom1,custom2,custom3,custom4 FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY CASE WHEN brand1 < brand2 THEN brand1||brand2||YEAR WHEN brand2 < brand1 THEN brand2||brand1||YEAR end ORDER BY year) rn FROM brands a WHERE a.custom1=a.custom3 AND a.custom2=a.custom4 ) WHERE rn=1 union SELECT brand1,brand2,YEAR,custom1,custom2,custom3,custom4 FROM brands a WHERE nvl(a.custom1,0)nvl(a.custom3,0) AND nvl(a.custom2,0)nvl(a.custom4,0) ;

@nehalmaheshwari1999 15 күн бұрын

@techTFQ Shouldn't the custom1 of one of the row be compared with the custom3 of the other row having same airID instead of the same row?

@Shruti_Gore 3 ай бұрын

Can you make 30 days sql easy to hard playlist

@devarapallivamsi7064 3 ай бұрын

Only 5 lines WITH rns AS ( SELECT *,ROW_NUMBER() OVER(PARTITION BY custom1,custom3) as rnk FROM brands ) SELECT * FROM rns WHERE rnk < 2

@surabhijagadish5210 4 ай бұрын

Made complex query too easy.

@phamnguyentrucgiang569 5 ай бұрын

@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?

@ananth.r5317 5 ай бұрын

Thanks lots..❤❤❤❤

@The_Narutoboy_15 5 ай бұрын

thank you sir

@jollymeelubari9045 5 ай бұрын

Thanks for this

@khushboobaghel6594 5 ай бұрын

Thankyou Sir

@srinubathina7191 5 ай бұрын

Thank You sir

@tornado9689 5 ай бұрын

My question is you can also select query with column name what you have done in last to fetch the data . What is the need of using cte then

@codeduel 5 ай бұрын

For Optimization sake

@priyankapatil6835 5 ай бұрын

big thanks techTFQ

@aswinc4829 5 ай бұрын

Thank you,it really helps

@krishkhemani96 4 ай бұрын

I am sure you love cases😂.Make a video on them please 😅

@ManiKandan-kg5ky 5 ай бұрын

Waiting bro

@jayavinayak2593 5 ай бұрын

Thank You

@techTFQ 5 ай бұрын

You're welcome

@smrutiranjansenapati1695 5 ай бұрын

Hi, Since it has example of 5-6 records, when we have large amount of records, how we can mak sure that the 1st condition if custom1 = custom3 & custom2 = custom4 will not be satisfied to eliminate the duplicate? Please reply. Thank you

@MrYeduguri 5 ай бұрын

Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?

@venkateshmallisetty4196 5 ай бұрын

it is very tipical to download the data set could you please help me with that

@user-pk7rp4ej9y 5 ай бұрын

With CTEs, we dont have to deal with subqueries!

@ehsanshakeri620 5 ай бұрын

Thank you

@lakshitgupta5064 5 ай бұрын

What if after creating the pairid, we just perform groupby operation based on pairid, custom1, custom2, custom3 and custom4 and then filter it by using having count = 1? Will this approach work?