I'm trying to calculate the average number of days between repeat purchases at customer level and I have two distinct queries to this effect but both of them give me two different answers. I can't tell whichh query is returning the correct result

Joshua Olufemi 1 Reputation point
2020-12-11T16:42:05.293+00:00

/4)Calculate the average number of days between repeat purchases at customer level/

SELECT [CustomerID],
AVG(CONVERT(decimal(7,2), DATEDIFF(DAY,PriorDate,[OrderDate]))) AS Avg_Time_BW_Purchases
FROM
(
SELECT [CustomerID],
[OrderDate],
LAG([OrderDate],1) OVER (PARTITION BY [CustomerID] ORDER BY [OrderDate]) AS PriorDate,
[OrderQty],
[LineTotal]
FROM [Sales].[SalesOrderDetail] AS A
LEFT JOIN [Sales].[SalesOrderHeader] AS B
ON A.SalesOrderID = B.SalesOrderID
) AS TempTable
GROUP BY [CustomerID],[OrderDate],PriorDate
ORDER BY Avg_Time_BW_Purchases DESC

/5)Calculate the average number of days between repeat purchases at customer level/

SELECT DISTINCT([CustomerID]), AVG([No. of Days Between Purchases]) OVER (PARTITION BY [CustomerID])
FROM(
SELECT [CustomerID],
[OrderDate],
CONVERT(DECIMAL(7,1), [OrderDate] - LAG([OrderDate],1) OVER (PARTITION BY [CustomerID] ORDER BY [OrderDate])) AS [No. of Days Between Purchases],
[OrderQty],
[LineTotal]
FROM [Sales].[SalesOrderDetail] AS A
LEFT JOIN [Sales].[SalesOrderHeader] AS B
ON A.SalesOrderID = B.SalesOrderID) AS Table1

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.8K Reputation points MVP
    2020-12-11T23:01:43.823+00:00

    The first one is wrong. If you look at your GROUP BY clause, you will understand why,

    (Yitzhak, you may recognize the table names from AdventureWorks.)

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-14T07:14:09.147+00:00

    Hi @Joshua Olufemi ,

    Thank you so much for posting here.

    Only the second query is working fine.

    You could compare the GROUP BY/PARTITION BY part of these two queries.

    The first one is GROUP BY [CustomerID],[OrderDate],PriorDate and the second one is PARTITION BY [CustomerID]. Your requirement is at customer level.

    You could modify the first query as below: 

    SELECT [CustomerID],  
AVG(CONVERT(decimal(7,2), DATEDIFF(DAY,PriorDate,[OrderDate]))) AS Avg_Time_BW_Purchases  
FROM  
(  
SELECT [CustomerID],  
[OrderDate],  
LAG([OrderDate],1) OVER (PARTITION BY [CustomerID] ORDER BY [OrderDate]) AS PriorDate,  
[OrderQty],  
[LineTotal]  
FROM [Sales].[SalesOrderDetail] AS A  
LEFT JOIN [Sales].[SalesOrderHeader] AS B  
ON A.SalesOrderID = B.SalesOrderID  
) AS TempTable  
GROUP BY [CustomerID]  
ORDER BY [CustomerID]

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments