Remove Redundant Pairs - SQL Interview Query 1 | SQL Problem Level "HARD"
Вставка
- Опубліковано 31 лип 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 UA-cam 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 UA-cam 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!
Simply amazing. Thanks, Thoufiq!
Awesome , Great learning Experience. Pls make more such challenges.
Very helpful . Thank you so much big bro for this series, just keep continue.🙏
amazing que and the way of solving is awesome.. thank u so much techTFQ!!
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.
still the best SQL video!!!! I love this idea, please continue!
Thankyou for guiding for 30 days it will help us to improove Sql.
Really, It's very nice 🙂 Thank you for this series.
extremely insightful.. thanks
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
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. 😊
Thank you for your amazing lectures and feedbacks and thank you for being so supportive!
Great solution. I'm afraid I am a bit late to start but I will definitely continue this!
I'm finally done with query 1 with your solution help. looking forward to cracking future questions on my own
great initiative and quality content 👍
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
super videos bro...keep posting
Great !!, Thank you
Thanks you Sir, it really helpful
Thank you so much for starting this series. 🙏
Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.
Bro pz upload 2 video per day 😊
Good question❤ starting it from today.
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.
Awesome TFQ
Love the way you teach ❤
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
Thank you,it really helps
Thank you for explaining thoroughly :-)
Fantastic 🌻🌻🌻
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.
Please also doo the sql interview questions for data analyst. Lots of love ❤
Nice video
Thanks for this
Thank You sir
This was pretty interesting
Thank you so much for SQL videos
Thankyou Sir
Amazing 🔥🎉
Glad you like it!
Great! video Thanks
Glad you liked it!
thank you sir
Thanks lots..❤❤❤❤
Thank You😊.
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
Amazing video sir, Thanks for your time
Waiting bro
Techtfq is back💯😎
We all are excited.
thanks
big thanks techTFQ
Good content!
Glad you think so!
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
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.
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)
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)')
Thank You
You're welcome
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);
Can you please make video on how to write dynamic query in pyspark.
That would be a lot of help.
Thank u❤
Awesome, Can you share excel page : it's good to store the results and everthing in excel
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.....
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:)
with tbl as
(
SELECT *,case when brand1
Thanks you😊
Welcome 😊
Hi May I ask a question?even thou you have filtered where rn = 1; , I still see one rn=2 in that column why?
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 😍
Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?
@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?
Can you suggest online SQL editor for practice?
I am sure you love cases😂.Make a video on them please 😅
I couldn't solve myself, but I find it easy after seeing the solution..not sure y.
❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅
Thank you sir that is extremely amazing❤❤❤❤❤❤❤
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?
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;
i was waiting for this
Hello , I am looking help from you ❤
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
More 8 mints to go
it is very tipical to download the data set could you please help me with that
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!
Bro pz upload 2 video per day😊
but how it check the condition custom1=custom3 and custom2-=custom4
?
@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);
????????????????
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)
Thanks a lot for the content..
WITH PAIRS AS(
SELECT *,CASE WHEN brand1
How will you know when to use CTE by looking at the question, sir?
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) ?
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
Bro @malcorub what if brand1 and brand2 length is same
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.
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
I try to download the dataset but I could not able to do it can you help me to download the data set
will you cover ORMs
Can we use self join instead here!
Done
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.
Where is the data set ?
Any way to download the data without using Discord? I am not good at Discord
By using dense_rank we can solve this