# 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

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
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
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

1. 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.)

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

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
ON A.SalesOrderID = B.SalesOrderID
) AS TempTable
GROUP BY [CustomerID]
ORDER BY [CustomerID]
``````

Best regards
Melissa