How to calculate average date occurrence frequency in SQL
I'm trying to produce a query on the following table (relevant portion only):
Create Table [Order] ( OrderID int NOT NULL IDENTITY(1,1), CreationDate datetime NOT NULL, CustomerID int NOT NULL )
I would like to see a list of CustomerIDs with each customer's average number of days between orders. I'm curious if this can be done with a pure set based solution or if a cursor/temp table solution is necessary.
;WITH base AS ( SELECT CustomerID, ROW_NUMBER() over (partition BY CustomerID ORDER BY CreationDate, OrderID) AS rn FROM [Order] ) SELECT b1.CustomerID, AVG(DATEDIFF(DAY,b1.CreationDate, b2.CreationDate) ) FROM base b1 JOIN base b2 ON b1.CustomerID=b2.CustomerID AND b2.rn =b1.rn+1 GROUP BY b1.CustomerID