Another Method can be as follows-------------------- I created my date column using recursive CTE table--------- WITH recursive Date_Ranges AS ( select '2022-01-01' as Date union all select Date + interval 1 day from Date_Ranges where Date < '2022-12-31'), tab as( select *,dayofweek(Date) as daynum,dayname(Date) dayname ,case when dayofweek(Date)=6 then row_number() over(partition by dayofweek(Date) order by dayofweek(Date)) else 0 end as FridayNum from Date_Ranges order by Date) select * from tab where mod(FridayNum,2)0;
[with r_cte as (select case when datepart(dw, '2023-01-01') > 6 then dateadd(day, 6 - datepart(dw, '2023-01-01') + 7, datepart(dw, '2023-01-01')) else dateadd(day, 6 - datepart(dw, '2023-01-01'), '2023-01-01') end as first_friday, 14 as days, 1 as factor from dual union all select dateadd(day, days*factor, first_friday), days, factor from r_cte where Year(dateadd(day, days*factor, first_friday)) = 2023) select * from r_cte];
#feedback: 1. Please type the code before recording the video; don't waste the viewer's time by typing, focus on the lesson instead of typing and correcting mistakes 2. Comment the code 3. Enable line numbers 4. Store constants in variables with proper names to make the code more readable 5. Indent the code
Thanks for the wonderful video. Please more Windows functions examples.
It was a good question, thanks for explaining. Same question has been asked to me with a little twist in an interview.
Glad it was helpful!
Great job. Thank you for your efforts.
Thank you
Thank you for your efforts. Nicely explained!
Glad it was helpful 🙏
Amazing. Very helpful.
Thank you
Excellent explanation.
Thank you
Another Method can be as follows-------------------- I created my date column using recursive CTE table---------
WITH recursive Date_Ranges AS (
select '2022-01-01' as Date
union all
select Date + interval 1 day
from Date_Ranges
where Date < '2022-12-31'),
tab as(
select *,dayofweek(Date) as daynum,dayname(Date) dayname
,case when dayofweek(Date)=6
then row_number() over(partition by dayofweek(Date) order by dayofweek(Date)) else 0 end as FridayNum
from Date_Ranges
order by Date)
select * from tab
where mod(FridayNum,2)0;
[with r_cte as (select case when datepart(dw, '2023-01-01') > 6 then dateadd(day, 6 - datepart(dw, '2023-01-01') + 7, datepart(dw, '2023-01-01'))
else dateadd(day, 6 - datepart(dw, '2023-01-01'), '2023-01-01')
end as first_friday,
14 as days,
1 as factor
from dual
union all
select dateadd(day, days*factor, first_friday),
days,
factor
from r_cte
where Year(dateadd(day, days*factor, first_friday)) = 2023)
select *
from r_cte];
#feedback:
1. Please type the code before recording the video; don't waste the viewer's time by typing, focus on the lesson instead of typing and correcting mistakes
2. Comment the code
3. Enable line numbers
4. Store constants in variables with proper names to make the code more readable
5. Indent the code
Loved points 2 to 5