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)
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.
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)
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.
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
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! 💻💡
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
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.
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
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
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);
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) ?
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!
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)')
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. 😊
Thank you so much. little modified : 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 (SELECT NULL)) as RKNO FROM brands ) SELECT * FROM CTE WHERE RKNO = 1 or (custom1 custom3 and custom2 custom4)
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 ".
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)
@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); ????????????????
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
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 as ( select * , row_number() over(partition by id order by id) as rn from ( SELECT * , case when brand1 > brand2 then concat(brand1, brand2, year) else concat(brand2, brand1, year) end as id , case when custom1 = custom3 or custom2 = custom4 then 1 else 0 end as flag FROM brands ) as t ) select * from cte where rn = 1 or (rn > 1 and flag = 0)
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 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 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.
Awesome , Great learning Experience. Pls make more such challenges.
I'm finally done with query 1 with your solution help. looking forward to cracking future questions on my own
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)
Great solution. I'm afraid I am a bit late to start but I will definitely continue this!
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.
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
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 😍
with tbl as
(
SELECT *,case when brand1
This is somewhat more clear
i think
Awesome TFQ
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:)
i was waiting for this
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.
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.
super videos bro...keep posting
extremely insightful.. thanks
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
Good question❤ starting it from today.
Thankyou for guiding for 30 days it will help us to improove Sql.
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.
Great !!, Thank you
Simply amazing. Thanks, Thoufiq!
Very helpful . Thank you so much big bro for this series, just keep continue.🙏
Amazing 🔥🎉
Glad you like it!
Please also doo the sql interview questions for data analyst. Lots of love ❤
Thank you for explaining thoroughly :-)
Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.
Bro pz upload 2 video per day 😊
still the best SQL video!!!! I love this idea, please continue!
amazing que and the way of solving is awesome.. thank u so much techTFQ!!
Really, It's very nice 🙂 Thank you for this series.
Thank you for your amazing lectures and feedbacks and thank you for being so supportive!
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)
Thanks you Sir, it really helpful
❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅
Thank you sir that is extremely amazing❤❤❤❤❤❤❤
Love the way you teach ❤
Intresting Question!
Im sure if anyone deligently follow this #30DaysSQLChallenge
his/her SQL skills drastically improves
Fantastic 🌻🌻🌻
Thank you so much for starting this series. 🙏
Techtfq is back💯😎
We all are excited.
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?
great initiative and quality content 👍
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! video Thanks
Glad you liked it!
This was pretty interesting
Nice video
Amazing video sir, Thanks for your time
Thanks lots..❤❤❤❤
Awesome, Can you share excel page : it's good to store the results and everthing in excel
Great. Thank you
Thanks for this
Thank you so much for SQL videos
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
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!
I couldn't solve myself, but I find it easy after seeing the solution..not sure y.
Thank you,it really helps
big thanks techTFQ
Thankyou Sir
Thank You😊.
Thank You sir
Good content!
Glad you think so!
Can you please make video on how to write dynamic query in pyspark.
That would be a lot of help.
Thank u❤
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)')
I am sure you love cases😂.Make a video on them please 😅
Thank You
You're welcome
Waiting bro
thanks
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. 😊
Thanks you😊
Welcome 😊
Thank you so much.
little modified :
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 (SELECT NULL)) as RKNO FROM brands )
SELECT * FROM CTE WHERE RKNO = 1
or (custom1 custom3 and custom2 custom4)
Great modification!
I think in where clause it should be OR in place of AND.
@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?
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
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 ".
Can you suggest online SQL editor for practice?
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)
Can you make 30 days sql easy to hard playlist
🤞🙏🏻
@techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?
what if the brand names start with the same letter?
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!
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.
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.
it is very tipical to download the data set could you please help me with that
Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?
Thanks a lot for the content..
WITH PAIRS AS(
SELECT *,CASE WHEN brand1
@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);
????????????????
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?
Bro pz upload 2 video per day😊
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
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
Can we use self join instead here!
with cte as
(
select
*
, row_number() over(partition by id order by id) as rn
from
(
SELECT
*
, case
when brand1 > brand2 then concat(brand1, brand2, year)
else concat(brand2, brand1, year)
end as id
, case
when custom1 = custom3 or custom2 = custom4 then 1
else 0 end as flag
FROM brands
) as t
)
select
*
from cte
where rn = 1 or (rn > 1 and flag = 0)
Made complex query too easy.
I think there should have been 'or' condition in the bracket instead of 'and'
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?