Remove Redundant Pairs - SQL Interview Query 1 | SQL Problem Level "HARD"
HTML-код
- Опубликовано: 25 июл 2024
- 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/?ref=thoufiqmoha...
Let's follow the below routine to make the best use of it:
1. Watch the RUclips 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 RUclips 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/blog/30daysqlquer...
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!
Awesome , Great learning Experience. Pls make more such challenges.
Very helpful . Thank you so much big bro for this series, just keep continue.🙏
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.
I'm finally done with query 1 with your solution help. looking forward to cracking future questions on my own
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
Simply amazing. Thanks, Thoufiq!
Thankyou for guiding for 30 days it will help us to improove Sql.
amazing que and the way of solving is awesome.. thank u so much techTFQ!!
Thank you for your amazing lectures and feedbacks and thank you for being so supportive!
still the best SQL video!!!! I love this idea, please continue!
Great solution. I'm afraid I am a bit late to start but I will definitely continue this!
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
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. 😊
extremely insightful.. thanks
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.
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.
Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.
Bro pz upload 2 video per day 😊
Thanks you Sir, it really helpful
Please also doo the sql interview questions for data analyst. Lots of love ❤
Really, It's very nice 🙂 Thank you for this series.
Good question❤ starting it from today.
super videos bro...keep posting
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
What will you do if the brand is of equal length?
Thank you so much for starting this series. 🙏
I love the idea! I will be tuning in every day this month.... even on weekends with hangover. LOL
Thats the spirit 😃
@@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.
@@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.
@@somnathdutta6311 no one will ever watch as there are many channels who solve and give answer there.
Great !!, Thank you
Awesome TFQ
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! 💻💡
Amazing 😍
Great. Thank you
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.
Thank you for explaining thoroughly :-)
great initiative and quality content 👍
Great! video Thanks
Glad you liked it!
Love the way you teach ❤
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)
Nice video
Thank you,it really helps
Fantastic 🌻🌻🌻
Thank You sir
Thanks for this
Thank you so much for SQL videos
Amazing 🔥🎉
Glad you like it!
Thankyou Sir
thank you sir
This was pretty interesting
Awesome, Can you share excel page : it's good to store the results and everthing in excel
Can you please make video on how to write dynamic query in pyspark.
That would be a lot of help.
Thank u❤
Amazing video sir, Thanks for your time
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)
as per the 3rd condition you need a little adjustment in your code (custom1 custom3 OR custom2 custom4)*
but thanks to your answer i got to know about STRCMP
Thank you
with tbl as
(
SELECT *,case when brand1
Thank You😊.
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
same brother:)
Waiting bro
Thanks lots..❤❤❤❤
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.....
thanks
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);
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)')
big thanks techTFQ
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?
@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?
Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?
Thank You
You're welcome
Techtfq is back💯😎
We all are excited.
Good content!
Glad you think so!
I couldn't solve myself, but I find it easy after seeing the solution..not sure y.
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!
it is very tipical to download the data set could you please help me with that
Can you suggest online SQL editor for practice?
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
How will you know when to use CTE by looking at the question, sir?
Bro @malcorub what if brand1 and brand2 length is same
Hi May I ask a question?even thou you have filtered where rn = 1; , I still see one rn=2 in that column why?
❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅
Thank you sir that is extremely amazing❤❤❤❤❤❤❤
@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);
????????????????
amazing!! can you tell me what to do in case of equal length of brand1 and brand2?
thanks in advance :)
you can put that condition in the same case along with < condition. Ultimately we are concatenating all the records in that cte.
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
For Optimization sake
Thanks you😊
Welcome 😊
but how it check the condition custom1=custom3 and custom2-=custom4
?
I try to download the dataset but I could not able to do it can you help me to download the data set
More 8 mints to go
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) ?
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;
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)
Awesome video. do you have video on sql performance ? if yes could you please share.
Can we use self join instead here!
I am sure you love cases😂.Make a video on them please 😅
Hello , I am looking help from you ❤
will you cover ORMs
Thanks a lot for the content..
WITH PAIRS AS(
SELECT *,CASE WHEN brand1
By using dense_rank we can solve this
Any way to download the data without using Discord? I am not good at Discord
Where is the data set ?
Intresting Question!
Im sure if anyone deligently follow this #30DaysSQLChallenge
his/her SQL skills drastically improves
Can you make 30 days sql easy to hard playlist
how about 100 days of database series,from 0 to 100 making people perfect in DB, i would like to join.
Can anyone explain me how to import data into SQL plz. I an unable to join discord server also
can anybody evaluate my code how efficient it is. because to me it looks pathetic..
with trialcte1 as (
with trialcte as(
SELECT *, case
when brand1 != brand2 and custom1 = custom3 and custom2 = custom4 then 'yes'
else 'no'
end as'topic'
FROM product_data)
select * , row_number() over(partition by topic order by year) as atmpt from trialcte)
select brand1 , brand2 , year , custom1 , custom2 , custom3 , custom4 from trialcte1
where (topic = 'no' and atmpt = 1) or
(topic = 'no' and atmpt = 2) or
(topic = 'no' and atmpt = 3) or
(topic = 'no' and atmpt = 4) or
(topic = 'yes' and atmpt = 1)
thanks for your help
what if the brand names start with the same letter?