Suppose we have a table which stores customers data. Given that a week contains 7 days, I covet to be able to start the week count from 1 the next day after each customer's create_date respectively. After which, bucket the cust_purchase_date in the appropriate week_number.
the week number breakdown should be different for every customer
cust_id create_date cust_purchase_date purchase_amount
111 7/5/2021 7/6/2021 85.00
111 7/5/2021 7/8/2021 25.00
111 7/5/2021 7/15/2021 35.00
111 7/5/2021 7/25/2021 15.00
222 7/9/2021 7/10/2021 10.00
222 7/9/2021 7/18/2021 25.00
222 7/9/2021 7/25/2021 31.00
222 7/9/2021 7/27/2021 41.00
333 7/11/2021 7/15/2021 51.00
333 7/11/2021 7/21/2021 65.00
444 7/15/2021 7/16/2021 100.00
444 7/15/2021 7/24/2021 78.00
444 7/15/2021 7/30/2021 87.00
555 8/20/2021 8/24/2021 71.00
555 8/20/2021 8/30/2021 55.00
555 8/20/2021 9/3/2021 36.00
555 8/20/2021 9/8/2021 25.00
Date Table (mocked - up):
cust_id create_date Wk 1 Start Wk 1 End Wk 2 Start Wk 2 End Wk 3 Start Wk 3 End....
111 7/5/2021 7/6/2021 7/12/2021 7/13/2021 7/19/2021 7/20/2021 7/26/2021...
222 7/9/2021 7/10/2021 7/16/2021 7/17/2021 7/23/2021 7/24/2021 7/30/2021...
333 7/11/2021 7/12/2021 7/18/2021 7/19/2021 7/25/2021 7/26/2021 8/1/2021...
444 7/15/2021 7/16/2021 7/22/2021 7/23/2021 7/29/2021 7/30/2021 8/5/2021...
555 8/20/2021 8/21/2021 8/27/2021 8/28/2021 9/3/2021 9/4/2021 9/10/2021...
Requested:
cust_id create_date cust_purchase_date purchase_amount week_number
111 7/5/2021 7/6/2021 85.00 1
111 7/5/2021 7/8/2021 25.00 1
111 7/5/2021 7/15/2021 35.00 2
111 7/5/2021 7/25/2021 15.00 3
222 7/9/2021 7/10/2021 10.00 1
222 7/9/2021 7/18/2021 25.00 2
222 7/9/2021 7/25/2021 31.00 3
222 7/9/2021 7/27/2021 41.00 3
333 7/11/2021 7/15/2021 51.00 1
333 7/11/2021 7/21/2021 65.00 2
444 7/15/2021 7/16/2021 100.00 1
444 7/15/2021 7/24/2021 78.00 2
444 7/15/2021 7/30/2021 87.00 3
555 8/20/2021 8/24/2021 71.00 1
555 8/20/2021 8/30/2021 55.00 2
555 8/20/2021 9/3/2021 36.00 3
555 8/20/2021 9/8/2021 25.00 3
CodePudding user response：
You can use
select
extract(week from '7/15/2021'::DATE) -
extract(week from '7/5/2021'::DATE) 1
as week_number;
week_number
-------------
2
CodePudding user response：
I do not know the purpose of your date_table but it seems an attempt to predetermine the week number for each customer with a column for each week. The problem with this approach is how many weeks do you generate columns for? What happens when a customer makes a purchase 5 years (or more) after the create_date. Further it is not necessary. Postgres allows date subtraction giving the number of days as an integer. Since your "week contains 7 days" simple division gives the week. (see demo).
select cust_id
, create_date
, purchase_date
, amount
, (purchase_date - create_date)/7 1 week_num
from customer_purchases;