CODEWITHSUNDEEP
sql
user3264602
user3264602

Reputation: 167

How do you find the average datediff by customer?

I have the following data:

CustomerID OrderDate
1          2011-02-16 
1          2011-04-20 
2          2011-04-25 
2          2011-10-24 
2          2011-11-14

How do I find the average DATEDIFF for each customer? The results I want would be the CustomerID and the average difference of dates between their orders. I really appreciate the help. This has had me stuck for months. Thank you in advance.

Additional Notes** I cannot use the lag function because of the server I am using.

Upvotes: 0

Views: 125

Answers (2)

user353gre3
user353gre3

Reputation: 2755

Assuming T is your table and you want average difference of dates in day, following is the code in SQL Server 2012:

with cte as (
SELECT CustomerID, OrderDate, 
    LAG(OrderDate) OVER (PARTITION BY CustomerID
    ORDER BY CustomerID, OrderDate) AS PrevDate
FROM T)
select customerid, avg(datediff(d, prevdate, orderdate )) as AvgDay
from cte group by customerid;

Upvotes: 1

Serpiton
Serpiton

Reputation: 3684

In SQLServer 2012 you can partition the lag changing the query of Binaya Regmi to

with cte as (
SELECT CustomerID, OrderDate,
    LAG(OrderDate) OVER (PARTITION BY CustomerID 
                         ORDER BY CustomerID, OrderDate) AS   PrevDate
FROM T)
Select customerid, avg(datediff(d, prevdate, orderdate)) average
From cte
Group By customerid

A query, non optimized, but using mostly standard sql (as the requester has not stated his RDBMS) is

SELECT customerid, avg(datediff(d,prevdate, OrderDate)) average
FROM (SELECT ext.customerid, ext.OrderDate, max(prevdate) prevdate
      FROM   orders ext
             INNER JOIN (SELECT customerid, orderdate prevdate
                         FROM   orders) sub
                        ON ext.customerid = sub.customerid
                           AND ext.OrderDate > sub.prevdate
      GROUP BY ext.customerid, orderdate) a
GROUP BY customerid

Upvotes: 2

Related Questions