Average day gap in between a repeat order for each product

Question

Can someone please help me to find the average time between first and second purchase on a product level.

This is what I have written -

Select A.CustomerId,A.ProductId , A.OrderSequence, (Case WHEN OrderSequence = 1 THEN OrderDate END) AS First_Order_Date,
MAX(Case WHEN OrderSequence = 2 THEN OrderDate END) AS Second_Order_Date
From
(
Select t.CustomerId, t.ProductId, t.OrderDate,
Dense_RANK() OVER (PARTITION BY t.CustomerId, t.ProductId ORDER BY OrderDate Asc) as OrderSequence  
From Transactions t (NOLOCK)
Where t.SiteKey = 01
Group by t.CustomerId, t.ProductId, t.OrderDate)
A
Where A.OrderSequence IN (1,2)
Group By A.Customer_Id, A.ProductId, A.OrderSequence, A.OrderDate

Sample Data:

Answer 1

It looks like row-numbering and LEAD should do the trick for you here.

• Don't use NOLOCK unless you really know what you're doing
• It's unclear if you want the results to be partitioned by CustomerId also. If not, you can remove it everywhere in the query

SELECT
  A.CustomerId,
  A.ProductId,
  AVG(DATEDIFF(day, OrderDate, NextOrderDate))
FROM
(
    SELECT
      t.CustomerId,
      t.ProductId,
      t.OrderDate,
      ROW_NUMBER() OVER (PARTITION BY t.CustomerId, t.ProductId ORDER BY OrderDate) AS rn,
      LEAD(OrderDate) OVER (PARTITION BY t.CustomerId, t.ProductId ORDER BY OrderDate) AS NextOrderDate
    FROM Transactions t
    WHERE t.SiteKey = '01'
) t
WHERE t.rn = 1
GROUP BY
  t.Customer_Id,
  t.ProductId;