CODEWITHSUNDEEP
sqlsql-server
gal leshem
gal leshem

Reputation: 561

Calculate average between rows in SQL by using lag and ignore first row

I am trying to write a SQL query that calculate the average days from purchase to purchase for all customers who made two or more purchases:

  Customer_ID     |   Average number of day
    1033          |            175
     11           |            334
    1100          |            202.5
    111           |            52.5

I succeeded to show all the purchase dates for all customers and calculate the days between purchase to purchase.

SELECT Customer_ID,  Order_Date Cur,
LAG(Order_Date, 1) OVER (ORDER BY Customer_ID) AS Previous,
DATEDIFF(day, LAG(Order_Date, 1) OVER (ORDER BY Customer_ID), Order_Date) 
[Days Between Purchases]
FROM Orders

enter image description here enter image description here

How can I ignore the first row per customer and calculate averages between purchase to purchase? (I have to use LAG in my answer

Upvotes: 1

Views: 1285

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Your script is almost OK. Just add the customer ID relation to it. Other wise first row all eact customer will not be NULL.

SELECT 
cur.Customer_ID, 
cur.Order_Date Cur,
previous.Order_Date Previous, 
DATEDIFF(day, previous.Order_Date, cur.Order_Date) [Days Between purchases]
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous 
    ON cur.RowNumber = previous.RowNumber+1 
    AND cur.customer_id = previous.customer_id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

The simplest method is aggregation and some arithmetic:

SELECT CustomerId,
       DATEDIFF(day, MIN(o.Order_Date), MAX(o.Order_date)) * 1.0 / NULLIF(COUNT(*) - 1, 0)
FROM Orders o
GROUP BY CustomerId
HAVING COUNT(*) >= 2;

In a sense, the "average days between orders" is a trick question. You think that you have to calculate the difference between each order and the next.

In fact, you just need to divide the total time from the first order to the last order by one less than the number of orders. I can let you work out why this works.

Upvotes: 1

Related Questions