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.)
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
/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
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.)
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