Calculate average days between orders

I would like to calculate the average days between several order dates from a table called Orders. For each CustomerID, what is the average days between orders. The sample table is as below (img):

CREATE TABLE #Orders(CustomerID int, OrderDate datetime);

INSERT #Orders(CustomerID, OrderDate) VALUES
(100,'20170114'),(100,'20170123'),(100,'20170129'),
(101,'20170202'),(101,'20170212');

I tried this query:

SELECT  CustomerID, AVG(OrderDate - PriorDate)
FROM (SELECT CustomerID, OrderDate
, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as PriorDate
FROM #Orders where CustomerID = 100)

However, this yields:

Msg 102, Level 15, State 1
Incorrect syntax near ')'.

Several issues:

• You are trying to take an average per customer, but you're not grouping by customer.
• Implicit math for datetimes (OrderDate-PriorDate) is not a good idea (try that with date or datetime2) - use DATEDIFF.
• Integer division will not give you an average like you think - try SELECT 5/2;. You need to convert at least one input to a decimal, either implicitly (*1.0) or explicitly (CONVERT(), TRY_CONVERT(), CAST(), etc.). Explicit allows you to control decimal places in some cases.
• LAG() was not complete - while the default is 1, I think it is good to be explicit that you want the previous row.
• The incorrect syntax error is actually coming from the end of the query - when you have something like SELECT ... FROM (<subquery>), that subquery needs to be named, so you need to use something like SELECT ... FROM (<subquery>) AS x; for example.

Try the following:

SELECT
CustomerID,
AvgLag = AVG(CONVERT(decimal(7,2), DATEDIFF(DAY, PriorDate, OrderDate)))
FROM
(
SELECT CustomerID, OrderDate, PriorDate = LAG(OrderDate,1)
OVER (PARTITION BY CustomerID ORDER BY OrderDate)
FROM #Orders
WHERE CustomerID = 100
) AS lagged
GROUP BY CustomerID;

Results:

CustomerID    AvgLag
----------    ------
100           7.50

Of course, if you want the average for all customers, just leave out the WHERE clause. But if you really only ever want a single customer, you don't really need the customer in the output, so you can adjust the query a little bit to get rid of the GROUP BY (let's parameterize the customer ID while we're at it):

DECLARE @CustomerID int = 100;

SELECT AvgLag = AVG(Lag), CustomerID = @CustomerID -- you may not need this
FROM
(
SELECT Lag = CONVERT(decimal(7,2), DATEDIFF(DAY, LAG(OrderDate,1)
OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate))
FROM #Orders
WHERE CustomerID = @CustomerID
) AS Lagged;

A way to simplify the query - if you only need the average lag between order dates - is to identify that you only need the dates of the first and last orders and the number of orders for each customer. If you have 11 orders for a customer, and a year between the first and the last order, the average is 365 / 10.

SELECT
CustomerID,
AvgLag = CASE WHEN COUNT(*) > 1 THEN
CONVERT(decimal(7,2),
DATEDIFF(day, MIN(OrderDate), MAX(OrderDate)))
/ CONVERT(decimal(7,2), COUNT(*) - 1)
ELSE NULL
END
FROM #Orders
GROUP BY CustomerID ;

I think this is basically the same as ypercube +1 (I did not see it)

select CustomerID
, cast(DATEDIFF(dd, min(OrderDate), max(OrderDate)) as decimal) / (count(*) - 1) as [avgDayDelta], count(*)
from #Orders
group by CustomerID
having count(*) > 1