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

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:

Let's follow the below routine to make the best use of it:
1. Watch the YouTube 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 YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

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!

[mindlessscroll]

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)

[onkarbarhate]

Thank you for your SQL learning videos! Watching them has significantly boosted my confidence, and I also subscribed to your SQL session on your techtfq website.

While going through one of your videos, I noticed a minor error: in the WHERE clause, the second condition should use 'OR' instead of 'AND'.

Just thought to share this feedback. Keep up the great work!
with cte as
(select *,
case when brand1 < brand2 then concat(brand1, brand2, year)
else concat(brand2, brand1, year)
end as new_grp_2
from brands),
cte_2 as
(select *, dense_rank() over(partition by new_grp_2 order by brand1) as rnk
from cte)
select cte_2.brand1, cte_2.brand2, cte_2.year, cte_2.custom1, cte_2.custom2, cte_2.custom3, cte_2.custom4
from cte_2
where rnk <> 2 or
(custom1 <> custom3 or custom2 <> custom4)

[prashanttripathi]

Main part was to make a pair of brands and I am happy that i solved that part. I love myself! Thanks

[vishalmaurya]

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

[malcorub]

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

[rahuldey]

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.

[splendidabhi]

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! 💻💡

[MwAVlogs]

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.

[junaidmahmud]

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

[ContentRoomB]

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

[aarizaziz]

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.

[beautifulworld]

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

[kalpeshispatil]

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

[pavanigoud]

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

[inderpanda]

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

[khushboobaghel]

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

[CricketLivejavwad]

Techtfq is back💯😎
We all are excited.

[dataengineeringsimplified]

with tbl as
(
SELECT *, case when brand1<brand2 then concat(brand1, brand2) else concat(brand2, brand1) end as pair FROM brands
),
tbl2 as
(
select *, row_number() over(partition by pair order by year) as rnk from tbl
)
select * from tbl2 where custom1=custom3 and custom2 = custom4 and rnk=1
union
select * from tbl2 where custom1 != custom3 OR custom2 != custom4
union
select * from tbl2 where brand1 is null OR brand2 is null
order by pair

[prithvirajpatil]

Good question❤ starting it from today.

[shwetamishra]

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