# Thread: Average duration to purchase again the same product

1. Registered User
Join Date
Sep 2015
Posts
2

## Unanswered: Average duration to purchase again the same product

Hi Everybody ,

I have data concerning sale of several products, columns are two : Name_product, Purchase_day

I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products

As in example, one product can be bought many times, so this product we will see it repeated several times at different days " Purchase_day " so here we can have Durations between each two successive purchases for the same product, but for the last purchase of this product the duration until today will be the day of yesterday minus the last day of purchase, and finally the average duration of this product will be the sum of all durations of this product / number of all durations of this product.

Link to the table copy and paste : https://onedrive.live.com/redir?resi...nt=file%2cxlsx

Thanks a lot for your help.

2. Registered User
Join Date
Sep 2015
Posts
2

## An ambitious try

Hi everybody,

Here is an ambitious try as I am not developer.

SELECT Name_product, Purchase_Day
CASE
when cur.Name_product = prev.Name_product
AND cur.Purchase_Day <= '31/05/2014' then cur.Purchase_Day - prev.Purchase_Day as Durarion
when cur.Name_product <> prev.Name_product
AND cur.Purchase_Day > '31/05/2014' then "" as Duration
else '31/05/2014' - prev.purchase_Day as Duration
END
FROM Sales
ORDER BY Name_product, Purchase_Day

Of course it is false but it's a try, this is without Average duration.

Thanks
Last edited by sellal; 09-08-15 at 10:14.

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,860
Have you looked into the LAG function?

Code:
```select name_product,
purchase_day,
lag (purchase_day, 1) over (partition by name_product order by purchase_day)
from Sales
order by name_product,
purchase_day```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•