How to create custom WEEKOFYEAR index in Snowflake?

I am trying to custom build a week index (1-52) The logic should be like:

1. Start with the 1st Saturday of January of a year
2. Build custom intervals from every Saturday (start of the week) to Fridays (end of the week) and increment the week index by 1.

Example:
Week 23 will include July 3 2021 – July 9 2021
Week 24 will include July 10 2021- July 16 2021 etc.

I tried using Select WEEKOFYEAR(date) from table but this format will be tied to the general ISO calendar, so it won’t match up with my custom week index aggregation. I hope someone can help.

Answer

Try this… I put in the comments that I think according to your week policy the weeks for July will actually be higher:

create or replace function MY_WEEK(D date)
returns number(38,0)
language sql
as
$$

floor((D - next_day(date_from_parts(date_part(y, D), 1, 1) - interval '1 day', 'saturday')) / 7 ) + 1

$$;

select MY_WEEK('2021-01-21');