CODEWITHSUNDEEP
sqlsql-serverdate
Natan
Natan

Reputation: 139

SQL How to calculate Average time between Order Purchases? (do sql calculations based on next and previous row)

I have a simple table that contains the customer email, their order count (so if this is their 1st order, 3rd, 5th, etc), the date that order was created, the value of that order, and the total order count for that customer.

Here is what my table looks like

Email          Order Date     Value Total
[email protected] 1   12/1/2016   85  5
[email protected] 2   2/6/2017    125 5
[email protected] 3   2/17/2017   75  5
[email protected] 4   3/2/2017    65  5
[email protected] 5   3/20/2017   130 5
[email protected] 1   2/12/2018   150 1
[email protected] 1   6/15/2018   36  3
[email protected] 2   7/16/2018   41  3
[email protected] 3   1/21/2019   140 3
[email protected] 1   8/10/2018   54  2
[email protected] 2   11/16/2018  65  2

What I want to do is calculate the time average between purchase for each customer. So lets take customer ylove. First purchase is on 6/15/18. Next one is 7/16/18, so thats 31 days, and next purchase is on 1/21/2019, so that is 189 days. Average purchase time between orders would be 110 days.

But I have no idea how to make SQL look at the next row and calculate based on that, but then restart when it reaches a new customer.

Here is my query to get that table:

SELECT 
F.CustomerEmail
,F.OrderCountBase
,F.Date_Created
,F.Total
,F.TotalOrdersBase
FROM #FullBase F 
ORDER BY f.CustomerEmail

If anyone can give me some suggestions, that would be greatly appreciated.

And then maybe I can calculate value differences (in percentage). So for example, ylove spent $36 on their first order, $41 on their second which is a 13% increase. Then their second order was $140 which is a 341% increase. So on average, this customer increased their purchase order value by 177%. Unrelated to SQL, but is this the correct way of calculating a metric like this?

Upvotes: 0

Views: 4906

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The simplest formulation is:

select email,
       datediff(day,  min(Order_Date), max(Order_Date)) / nullif(total-1, 0) as avg_days
from t  
group by email;

You can see this is the case. Consider three orders with od1, od2, and od3 as the order dates. The average is:

( (od2 - od1) + (od3 - od2) ) / 2

Check the arithmetic:

--> ( od2 - od1 + od3 - od2 ) / 2
--> ( od3 - od1 ) / 2

This pretty obviously generalizes to more orders.

Hence the max() minus min().

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

looking to your sample you clould try using the diff form min and max date divided by total

select email, datediff(day,  min(Order_Date), max(Order_Date))/(total-1) as avg_days
from your_table  
group by  email

and for manage also the one order only

select email, 
  case when  total-1 > 0 then  
    datediff(day,  min(Order_Date), max(Order_Date))/(total-1) 
  else  datediff(day,  min(Order_Date), max(Order_Date)) end as avg_days
from your_table  
group by  email

Upvotes: 3

Related Questions