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.
SELECT Name_product, Purchase_Day
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
ORDER BY Name_product, Purchase_Day
Of course it is false but it's a try, this is without Average duration.