Simply amazing. Thanks, Thoufiq!

Awesome , Great learning Experience. Pls make more such challenges.

still the best SQL video!!!! I love this idea, please continue!

Very helpful . Thank you so much big bro for this series, just keep continue.🙏

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!!

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.

Awesome TFQ

Thank you for your amazing lectures and feedbacks and thank you for being so supportive!

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.

Really, It's very nice 🙂 Thank you for this series.

super videos bro...keep posting

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

Great solution. I'm afraid I am a bit late to start but I will definitely continue this!

Thanks you Sir, it really helpful

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. 😊

Good question❤ starting it from today.

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

Thank you so much for starting this series. 🙏

Great !!, Thank you

Nice video

great initiative and quality content 👍

Fantastic 🌻🌻🌻

Please also doo the sql interview questions for data analyst. Lots of love ❤

Subscribed right away. Great content. Can't wait to watch more of your videos this weekend itself.

@sabmelegarebaba1233

3 ай бұрын

Bro pz upload 2 video per day 😊

Love the way you teach ❤

Great. Thank you

Waiting bro

Great! video Thanks

@techTFQ

3 ай бұрын

Glad you liked it!

Thank You sir

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.

Thankyou Sir

Thank you,it really helps

thank you sir

I love the idea! I will be tuning in every day this month.... even on weekends with hangover. LOL

@techTFQ

3 ай бұрын

Thats the spirit 😃

@somnathdutta6311

3 ай бұрын

@@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.

@umangbhatnagar1415

2 ай бұрын

@@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.

@Useruse867

22 күн бұрын

@@somnathdutta6311 no one will ever watch as there are many channels who solve and give answer there.

Thanks for this

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)

@gauravtanwar8886

Ай бұрын

as per the 3rd condition you need a little adjustment in your code (custom1 custom3 OR custom2 custom4)*

@gauravtanwar8886

Ай бұрын

but thanks to your answer i got to know about STRCMP

Amazing 🔥🎉

@techTFQ

3 ай бұрын

Glad you like it!

This was pretty interesting

Thank you so much for SQL videos

Thank you

thanks

Thank You😊.

Thanks lots..❤❤❤❤

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! 💻💡

@techTFQ

3 ай бұрын

Amazing 😍

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 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

@shabbiransari9731

20 күн бұрын

What will you do if the brand is of equal length?

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)

Amazing video sir, Thanks for your time

Techtfq is back💯😎 We all are excited.

big thanks techTFQ

Thank You

@techTFQ

3 ай бұрын

You're welcome

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.

with tbl as ( SELECT *,case when brand1

Good content!

@techTFQ

3 ай бұрын

Glad you think so!

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

@amlansasmal

3 ай бұрын

same brother:)

Can you please make video on how to write dynamic query in pyspark. That would be a lot of help. Thank u❤

❤ 30days of sql >>>>75 days of 😊 hard चैलेंज 😅 Thank you sir that is extremely amazing❤❤❤❤❤❤❤

Thanks you😊

@techTFQ

3 ай бұрын

Welcome 😊

Hello Thoufiq.. Thank you so much for sharing great content. Are you offering online training for sql & plsql?

More 8 mints to go

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.....

I couldn't solve myself, but I find it easy after seeing the solution..not sure y.

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)')

Hello , I am looking help from you ❤

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);

Done

Awesome, Can you share excel page : it's good to store the results and everthing in excel

I am sure you love cases😂.Make a video on them please 😅

​ @techTFQ Hi, thanks for uploading useful videos. I want to ask: this is easy, medium or hard question?

will you cover ORMs

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😊

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) ?

Thanks a lot for the content.. WITH PAIRS AS( SELECT *,CASE WHEN brand1

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?

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

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)

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)

By using dense_rank we can solve this

Made complex query too easy.

amazing!! can you tell me what to do in case of equal length of brand1 and brand2? thanks in advance :)

@bhanugoyal428

3 ай бұрын

you can put that condition in the same case along with < condition. Ultimately we are concatenating all the records in that cte.

it is very tipical to download the data set could you please help me with that

Bro @malcorub what if brand1 and brand2 length is same

How will you know when to use CTE by looking at the question, sir?

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

@Ikhideifidon

3 ай бұрын

For Optimization sake

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 you make 30 days sql easy to hard playlist

Sir can u make videos on trigger,Index and match in SQL

@techTFQ

3 ай бұрын

Yes, soon

@vikrantlonkar2898

3 ай бұрын

Thanks a lot I will be waiting for it

I try to download the dataset but I could not able to do it can you help me to download the data set

i was waiting for this

how about 100 days of database series,from 0 to 100 making people perfect in DB, i would like to join.

@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); ????????????????

Can we use self join instead here!

Where is the data set ?

with cte as ( select *, case when custom1 = custom3 and custom2 = custom4 then 'match' else 'not_matched' end as is_match, row_number()over(order by (select null)) as rn, row_number()over(partition by year order by (select null)) as rn_1 from brands ), cte1 as ( select *, (rn - rn_1) as pair from cte ) select min(brand1) as brand1, max(brand2) as brand2, max(year) as year, max(custom1) as custom1, max(custom2) as custom2, max(custom3) as custom3, max(custom4) as custom4 from cte1 where is_match = 'match' group by pair union all select brand1, brand2, year, custom1, custom2, custom3, custom4 from cte1 where true and is_match != 'match';

With CTEs, we dont have to deal with subqueries!

I think there should have been 'or' condition in the bracket instead of 'and'

Hi May I ask a question?even thou you have filtered where rn = 1; , I still see one rn=2 in that column why?