@@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)
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.
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)
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.
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
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);
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. 😊
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! 💻💡
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) ?
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
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!
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)
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
My Solution with Cte as (Select *, Row_Number() over () as id ,case when Custom1=Custom3 and custom2=custom4 then 1 else 0 end as Type from brands) Select T1.brand1,T1.brand2,T1.year,T1.custom1,T1.custom2,T1.custom3,T1.custom4 from Cte T1 join Cte T2 on T1.brand1 =T2.brand2 Where T1.id
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)
@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); ????????????????
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
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?
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 believe below query will give the same result. Select * from brands where custom1=custom3 and custom2=custom4 fetch first 1 row UNION select * from brands where custom1 != custom3 or custom2 != custom4 UNION Select * from brands where custom3 is null or custom4 is null
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)
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.....
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, 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 *, 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 cte as (select *, case when brand1 < brand2 then brand1||''||brand2||''|| year else brand2||''||brand1||''|| year end as pair from brands) select brand1,brand2,year,custom1,custom2,custom3,custom4 from ( select *, case when custom1 = custom3 then row_number() over(partition by pair) else dense_rank() over(partition by pair) end as rkk from cte) where rkk = 1
My approach: SELECT brand1,brand2,YEAR,custom1,custom2,custom3,custom4 FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY CASE WHEN brand1 < brand2 THEN brand1||brand2||YEAR WHEN brand2 < brand1 THEN brand2||brand1||YEAR end ORDER BY year) rn FROM brands a WHERE a.custom1=a.custom3 AND a.custom2=a.custom4 ) WHERE rn=1 union SELECT brand1,brand2,YEAR,custom1,custom2,custom3,custom4 FROM brands a WHERE nvl(a.custom1,0)nvl(a.custom3,0) AND nvl(a.custom2,0)nvl(a.custom4,0) ;
