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)
@@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.
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)
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.
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.
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! 💻💡
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
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
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)
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
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);
with cte as ( Select *, row_number() over(partition by case when brand1>brand2 then concat(brand1, brand2, year) else concat(brand2, brand1, year) end ) as rn from brands) select * from cte where rn=1 or custom1!= custom3 or custom2!=custom4
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)')
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) ?
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!
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.....
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. 😊
MYSQL solution: with cte as (select *, case when brand1 < brand2 then concat(brand1,'-',brand2) else concat(brand2,'-',brand1) end as pair from brands) , cte2 as (select *, row_number() over(partition by pair,year order by (select null)) as rn from cte) select brand1, brand2, year, custom1, custom2, custom3, custom4 from cte2 where case when (custom1 = custom3) or (custom2 = custom4) then rn = 1 else rn >= 1 end;
@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 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)
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 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?
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)
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
Here is my Solution: -- Define the first CTE to calculate the next brand values WITH cte AS ( SELECT *, LEAD(brand2, 1, NULL) OVER (PARTITION BY year ORDER BY year) AS next_brand, LEAD(brand1, 1, NULL) OVER (PARTITION BY year ORDER BY year) AS next_brand1 FROM brands ), -- Define the second CTE to categorize each row cte2 AS ( SELECT *, CASE WHEN brand1 = next_brand AND brand2 = next_brand1 AND custom1 = custom3 AND custom2 = custom4 THEN 'group' ELSE 'not a group' END AS GroupType FROM cte ) -- Select rows where GroupType is 'not a group' SELECT brand1, brand2, year, custom1, custom2, custom3, custom4 FROM cte2 WHERE GroupType = 'not a group'; Please suggest if it is a good solution or not?
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
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)
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.
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
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 , Great learning Experience. Pls make more such challenges.
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
Great solution. I'm afraid I am a bit late to start but I will definitely continue this!
Very helpful . Thank you so much big bro for this series, just keep continue.🙏
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 😍
Simply amazing. Thanks, Thoufiq!
Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.
Bro pz upload 2 video per day 😊
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
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
still the best SQL video!!!! I love this idea, please continue!
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.
amazing que and the way of solving is awesome.. thank u so much techTFQ!!
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)
with tbl as
(
SELECT *,case when brand1
This is somewhat more clear
i think
Awesome TFQ
super videos bro...keep posting
Please also doo the sql interview questions for data analyst. Lots of love ❤
Really, It's very nice 🙂 Thank you for this series.
Thankyou for guiding for 30 days it will help us to improove Sql.
extremely insightful.. thanks
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.
Good question❤ starting it from today.
Great !!, Thank you
Thank you for your amazing lectures and feedbacks and thank you for being so supportive!
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:)
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);
Love the way you teach ❤
Thank you so much for starting this series. 🙏
Amazing 🔥🎉
Glad you like it!
Thank you for explaining thoroughly :-)
great initiative and quality content 👍
Techtfq is back💯😎
We all are excited.
Great. Thank you
Thanks you Sir, it really helpful
with cte as (
Select *,
row_number() over(partition by case when brand1>brand2 then concat(brand1, brand2, year)
else concat(brand2, brand1, year) end ) as rn
from brands)
select * from cte
where rn=1
or custom1!= custom3 or custom2!=custom4
Fantastic 🌻🌻🌻
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)')
Great! video Thanks
Glad you liked it!
Nice video
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) ?
I had same question, I think just an error. Output is the same in this case but given other entries the output would be different
Thank you so much for SQL videos
Awesome, Can you share excel page : it's good to store the results and everthing in excel
Thanks for this
Thanks lots..❤❤❤❤
❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅
Thank you sir that is extremely amazing❤❤❤❤❤❤❤
Thank you,it really helps
This was pretty interesting
Waiting bro
Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?
Can you please make video on how to write dynamic query in pyspark.
That would be a lot of help.
Thank u❤
Thanks sir
@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?
i was waiting for this
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.
Thank You sir
Thankyou Sir
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!
Thank You😊.
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.....
Amazing video sir, Thanks for your time
Good content!
Glad you think so!
Thank You
You're welcome
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. 😊
@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?
MYSQL solution:
with cte as (select *, case when brand1 < brand2 then concat(brand1,'-',brand2)
else concat(brand2,'-',brand1) end as pair from brands)
, cte2 as (select *, row_number() over(partition by pair,year order by (select null)) as rn from cte)
select brand1, brand2, year, custom1, custom2, custom3, custom4 from cte2
where case when (custom1 = custom3) or (custom2 = custom4) then rn = 1
else rn >= 1 end;
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 😊
thanks
I am sure you love cases😂.Make a video on them please 😅
🤞🙏🏻
@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 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)
Can you suggest online SQL editor for practice?
Awesome video. do you have video on sql performance ? if yes could you please share.
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?
big thanks techTFQ
Sir can u make videos on trigger,Index and match in SQL
Yes, soon
Thanks a lot I will be waiting for it
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?
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)
I couldn't solve myself, but I find it easy after seeing the solution..not sure y.
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
it is very tipical to download the data set could you please help me with that
hi taufiq, Your videos are helping me alot, but your blog has not been opening for last few days. Please do needfull
How will you know when to use CTE by looking at the question, sir?
how about 100 days of database series,from 0 to 100 making people perfect in DB, i would like to join.
Bro @malcorub what if brand1 and brand2 length is same
While using concat () in MySql why am I getting NULL for pair_id?
use this code ---just change case condition
with cte1 as( select *,
case
when brand1
@@SwarajLandge-c7f okay. Thanks for your help!
Thanks a lot for the content..
WITH PAIRS AS(
SELECT *,CASE WHEN brand1
Based on the PS shouldn't it be `where rn=1 or custom1 custom3 or custom2 custom4 `?
Yes, it should be. I was looking for this comment before putting up.
Hi May I ask a question?even thou you have filtered where rn = 1; , I still see one rn=2 in that column why?
what if the brand names start with the same letter?
Can we use self join instead here!
Here is my Solution:
-- Define the first CTE to calculate the next brand values
WITH cte AS (
SELECT *,
LEAD(brand2, 1, NULL) OVER (PARTITION BY year ORDER BY year) AS next_brand,
LEAD(brand1, 1, NULL) OVER (PARTITION BY year ORDER BY year) AS next_brand1
FROM brands
),
-- Define the second CTE to categorize each row
cte2 AS (
SELECT *,
CASE
WHEN brand1 = next_brand
AND brand2 = next_brand1
AND custom1 = custom3
AND custom2 = custom4 THEN 'group'
ELSE 'not a group'
END AS GroupType
FROM cte
)
-- Select rows where GroupType is 'not a group'
SELECT brand1,
brand2,
year,
custom1,
custom2,
custom3,
custom4
FROM cte2
WHERE GroupType = 'not a group';
Please suggest if it is a good solution or not?
I try to download the dataset but I could not able to do it can you help me to download the data set
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