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.

Answers


;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

Need Your Help

How to write the actual code from a nested class outside the main class

c++ class nested

I would like to keep the code readable by writing the actual code of a nested class outside the main class, Is it possible, and how ?

Access this STDCLASS object in php

php arrays oop curl stdclass

How to access objects with @ sign in stdclass i have tried this in php with curl:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.