Calculating average time (in days) between customer first and second order on BigQuery

I’m trying to figure out the avg days it takes for a customer to repeat purchase.

Here is what I have

  Email_address, 
  Order_name, 
  Date, 
  Order_number,
  lead(date) over (partition by email_address order by Order_number) next_order_date,
  lead(Order_number) over (partition by email_address order by Order_number) next_order_number
 FROM `dbOrders` 
Group by 1,2,3,4 

It’s returning this

 [
  {
    "Email_address": "[e-mail]",
    "Order_name": "#1234",
    "Date": "2019-11-08 00:18:00 UTC",
    "Order_number": "1",
    "next_order_date": "2019-11-28 20:54:00 UTC",
    "next_order_number": "2"
  },

I want to now get the average days between all first and second purchases, then second and third et

Sample data set

|---------------------|------------------|------------------|------------------| | Email_address | Order_name | Order_number | Date | |---------------------|------------------|------------------|------------------| | [e-mail] | 364 | 1 | 2019-08-11 | |---------------------|------------------|------------------|------------------| | [e-mail] | 366 | 2 | 2019-08-29 | |---------------------|------------------|------------------|------------------| | [e-mail] | 495 | 1 | 2019-09-04 | |---------------------|------------------|------------------|------------------| | [e-mail] | 564 | 2 | 2019-10-04 | |---------------------|------------------|------------------|------------------|

I want to find the avg time difference between the orders of both customers. [e-mail] = 18 days [e-mail] = 30 days

Average = 24 days

Answer

You seem to be asking for an aggregation query:

select seqnum,
       avg(date_diff(date, next_date, day)) as avg_days_to_next_order
from (select o.*,
             row_number() over (partition by email_address order by date) as seqnum,
             lead(date) over (partition by email_address order by date) as next-date
      from `dbOrders`  o
     ) o
group by seqnum
order by seqnum;