6 Replies Latest reply on Jul 17, 2014 7:43 AM by Matt Lutton

    Calculate last state/product for each customer/user

    istvn.rechner

      Hello All,

       

      I have a daily data about customers purchasing items/products. I want to calculate the last purchased product (or last state, etc.) for each customer. For example:

       

      The original data:

       

      customer_id

      day

      item_purchased

      1

      2014-01-01

      A

      1

      2014-01-05

      A

      1

      2014-01-07

      C

      2

      2013-05-01

      A

      2

      2013-05-03

      B

      3

      2014-06-04

      C

      3

      2014-06-05

      B

      3

      2014-06-06

      A

      3

      2014-06-09

      C

      3

      2014-06-12

      A

       

      The result would be:

       

      customer_id

      day

      item_purchased

      last_item_purchased

      1

      2014-01-01

      A

      C

      1

      2014-01-05

      A

      C

      1

      2014-01-07

      C

      C

      2

      2013-05-01

      A

      B

      2

      2013-05-03

      B

      B

      3

      2014-06-04

      C

      A

      3

      2014-06-05

      B

      A

      3

      2014-06-06

      A

      A

      3

      2014-06-09

      C

      A

      3

      2014-06-12

      A

      A

       

      I tried to use last() and index() functions, but couldn't figure it out. In SQL, this would be a self join on max(day), but I don't think that a blending would be the solution. Can I do this with one table calc?

       

      Thank you,