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,897 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    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 No 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 No comments