January 1st = Week 1

The below gives me week numbers where week 1 starts on 1/4/2021

date_trunc('week', transaction_date)     as week_number

1. How can I create a week_number where the week starts on January 1st and counts up 7 days for every week thereafter (for every year)?

2. And round up/down to 52 weeks at the end of the year?

Code attempted: This doesn’t give me the answer, but I’m thinking something like this might work…

ceil(extract(day from transaction_date)/7) as week_number

Expected Output:

transaction_date	week_number
1/1/2020	1
1/8/2020	2
…	…
12/31/2020	52
1/1/2021	1
1/8/2021	2
…	…
12/27/2021	52
12/28/2021	52
12/29/2021	52
12/30/2021	52
12/31/2021	52
1/1/2022	1

Thanks in advance!

Answer

A simple way is to use date arithmetic:

select 1 + (transaction_date - date_trunc('year', transaction_date)) / 7 as year_week