Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    2

    Cool 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. #2
    Join Date
    Sep 2015
    Posts
    2

    Cool 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 11:14.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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
  •