Calculating the average time between orders for each customer

Question

I've searched all over but cant seem to find how to calculate the average time between orders for each customer. I'm using Oracle SQL Developer. Ive tried using the lag() function with no joy.

Example data below shows a customer bought on 3 separate occasions. The average time between all purchases is 7.5 days ((6+9)/2).

CustID   OrderDate 
-------  ---------
1000000  14-AUG-12
1000000  23-AUG-12
1000000  29-AUG-12

So how do I calculate that average for each customer?

any help would be much appreciated.

Answer 1

If only the mean average is needed then I think you can get away with a simple query without window functions by looking at the min and max dates and then diving by the number of gaps between orders:

SELECT CustID, (MAX(OrderDate) - MIN(OrderDate)) / (COUNT(*) - 1)
  FROM Orders
 GROUP BY CustID
HAVING COUNT(*) > 1

Answer 2

the key here is the lag analytic function

select cust_id , avg(orderdate - lag_orderdate) as avg_time_between_orders
from (
    select cust_id , orderDate , lag(orderdate) over (partition by cust_id) as lag_orderdate
    from  orders )