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)
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)
@@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.
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.
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)
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.
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 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
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
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) ?
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! 💻💡
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 order by year) as rn from brands) Select * from cte rn where rn=1 or (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); ????????????????
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)')
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;
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, 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
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)
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?
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. 😊
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?
This solution only works when first pair is "custom1 = custom3 OR custom2 = custom4 ". If the first pair were "custom1 != custom3 OR custom2 != custom4 " and the second pair were "custom1 = custom3 OR custom2 = custom4 ". then it would return only one pair despite one of the pair is "custom1 != custom3 OR custom2 != custom4 ".
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
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)
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
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.
Awesome , Great learning Experience. Pls make more such challenges.
Great solution. I'm afraid I am a bit late to start but I will definitely continue this!
extremely insightful.. thanks
super videos bro...keep posting
I'm finally done with query 1 with your solution help. looking forward to cracking future questions on my own
Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.
Bro pz upload 2 video per day 😊
Simply amazing. Thanks, Thoufiq!
Awesome TFQ
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.
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)
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.
Amazing 🔥🎉
Glad you like it!
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
Good question❤ starting it from today.
Really, It's very nice 🙂 Thank you for this series.
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
amazing que and the way of solving is awesome.. thank u so much techTFQ!!
still the best SQL video!!!! I love this idea, please continue!
Thanks you Sir, it really helpful
Great !!, Thank you
with tbl as
(
SELECT *,case when brand1
This is somewhat more clear
i think
Thankyou for guiding for 30 days it will help us to improove Sql.
great initiative and quality content 👍
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.
Love the way you teach ❤
Please also doo the sql interview questions for data analyst. Lots of love ❤
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:)
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 :-)
Fantastic 🌻🌻🌻
This was pretty interesting
i was waiting for this
Thank you so much for starting this series. 🙏
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
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 😍
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);
Great. Thank you
Thanks for this
Amazing video sir, Thanks for your time
❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅
Thank you sir that is extremely amazing❤❤❤❤❤❤❤
Thanks lots..❤❤❤❤
Techtfq is back💯😎
We all are excited.
Good content!
Glad you think so!
Thank you,it really helps
Thank you so much for SQL videos
Thankyou Sir
Thank You sir
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)
@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?
Awesome, Can you share excel page : it's good to store the results and everthing in excel
@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);
????????????????
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)')
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;
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😊.
Thank You
You're welcome
Waiting bro
Can you please make video on how to write dynamic query in pyspark.
That would be a lot of help.
Thank u❤
big thanks techTFQ
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?
Can you suggest online SQL editor for practice?
Hi May I ask a question?even thou you have filtered where rn = 1; , I still see one rn=2 in that column why?
Bro @malcorub what if brand1 and brand2 length is same
Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?
thanks
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)
it is very tipical to download the data set could you please help me with that
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 a lot for the content..
WITH PAIRS AS(
SELECT *,CASE WHEN brand1
Amazing problem solution 🫶🏻
Thank you :)
@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?
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!
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?
I couldn't solve myself, but I find it easy after seeing the solution..not sure y.
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.....
with base as (select *,
case when brand1
Thanks you😊
Welcome 😊
I am sure you love cases😂.Make a video on them please 😅
I try to download the dataset but I could not able to do it can you help me to download the data set
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.
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. 😊
what if the brand names start with the same letter?
hi taufiq, Your videos are helping me alot, but your blog has not been opening for last few days. Please do needfull
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.
but how it check the condition custom1=custom3 and custom2-=custom4
?
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?
Intresting Question!
Im sure if anyone deligently follow this #30DaysSQLChallenge
his/her SQL skills drastically improves
This solution only works when first pair is "custom1 = custom3 OR custom2 = custom4 ". If the first pair were "custom1 != custom3 OR custom2 != custom4 " and the second pair were "custom1 = custom3 OR custom2 = custom4 ". then it would return only one pair despite one of the pair is "custom1 != custom3 OR custom2 != custom4 ".
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
🤞🙏🏻
how about 100 days of database series,from 0 to 100 making people perfect in DB, i would like to join.
Can we use self join instead here!