calculate the 7 day sale by different start date (the first day customer purchase), to find average purchase unit of each customer by every 7 days

From the table，I have the first table now, and trying to get every 7 day sales from the first day of this customer make the purchase. example is table 2

The final table shud be like this:

then I can calculate the average sales per customer

the hard part is:

1. every customer have different first day of purchase

2. The purchase date are not consequent, so I can't use unbonded or following 6 rows etc.

3. There're 5 years in the whole dataset, so I can't manually -7, -14, etc

4. I tried to use date_trunc('week',date, min(date) over (partition by customerid))

5. also tried parition by rows between 6 proceding and current row. But the date are not consequent so doesn't work

You can use SQL window function with 2 steps to get the result you want:

Step 1. Apply a window parition by per customer and get first_purchase_date for each customer. After that, use Presto date_diff() fucntion to calculate the date difference from first purchase date to current purchase date. Devide it by 7 to get week_bucket from the first date of purchase.

Step 2. group by per (customer, customer_sale_week_bucket) and get sum(sales_unit) and min(purchase_date) in each (customer, customer_sale_week_bucket) parition.

Here is the query:

with orders_with_customer_week_bucket AS 
(
  select
   purchase_date,
   customer_id,
   sales_unit,
   date_diff(day,min(purchase_date) over (partition by customer_id), purchase_date) / 7 as customer_sale_week_bucket
from
   orders
 )
 select
     purchase_week,
     customer_id,
     seven_day_sales_unit
 from
     (select
         customer_id,
         customer_sale_week_bucket,
         min(purchase_date) as purchase_week,
         sum(sales_unit) as seven_day_sales_unit
     from
        orders_with_customer_week_bucket
     GROUP BY
        customer_id,
        customer_sale_week_bucket
     )r

You can do it with a case statement looking at the dates. I did this in SQL Server but I believe it works in Presto. I think DATEADD might need to be "Date_Add" (with the quotes) in Presto.

Also you mentioned you may need 14 days so I added a column for that. You can see it's just a matter of changing the days in the DateAdd function.

FIDDLE

   SELECT t1.purchaseDate,
          t1.CustomerID,
          t1.SalesUnit,
          SUM(CASE 
                 WHEN t2.purchaseDate BETWEEN DATEADD(DAY, -6, t1.purchaseDate) AND t1.purchaseDate THEN t2.salesUnit 
               END) AS SalesLast7,
          SUM(CASE 
                 WHEN t2.purchaseDate BETWEEN DATEADD(DAY, -13, t1.purchaseDate) AND t1.purchaseDate THEN t2.salesUnit 
               END) AS SalesLast14  
     FROM temp t1
LEFT JOIN temp t2 ON t1.customerID = t2.customerID AND t2.purchaseDate IS NOT NULL
 GROUP BY t1.purchaseDate, t1.customerID, t1.salesUnit

I don't believe your sample data is tallied right. It's not clear to me if you truly need the cumulative values per week or just a straight total. My guess is that you want something in one of these two options.

with salesweek as (
    select *,
        -- weeks based on repeating 7-day counter starting at first purchase
        date_diff('day',
            min(purchasedate) over (partition by customerid),
            purchasedate) / 7 + 1 as weeknum -- integer divide
    from T
)
select customerid, sum(salesunit) * 1.0 / count(distinct weeknum) as avg7daysales
from salesweek
group by customerid;

Or possibly:

with firstpurchase as (
    select *,
        min(purchasedate) over (partition by customerid) as firstpurchasedate
    from T
  ), salesweek as (
    select *,
        -- weeks based on repeating 7-day counter starting at first purchase
        date_diff('day', firstpurchasedate, purchasedate) / 7 + 1 as weeknum
    from firstpurchase
  ), weighted as (
    select customerid, weeknum, purchasedate,
        sum(salesunit) * count(1) over (
            partition by customerid, weeknum
            order by purchasedate
            rows between current row and unbounded following
        ) as weightedsales
    from salesweek
    group by customerid, weeknum, purchasedate
)
select customerid, sum(weightedsales) * 1.0 / count(distinct weeknum)
from weighted
group by customerid
order by customerid;

https://dbfiddle.uk/sgOOd07H

This is in SQL Server since I don't have access to Presto.