sqlsap-iq

Sybase IQ 16 - Find members who have changed product

My data captures members purchasing history for a category of products. Typically a member will be buying product X for a number of months but I am interested in those members who moved to product Y and when that happened.

My data looks as follows:

Member Number   Date        Product                             TOT
    210539662   2019-05-26  PRODUCT A                           PREVENTATIVE
    210539662   2019-06-28  PRODUCT A                           PREVENTATIVE
    210539662   2019-07-30  PRODUCT A                           PREVENTATIVE
    210539662   2019-08-28  PRODUCT A                           PREVENTATIVE
    210539662   2019-09-24  PRODUCT A                           PREVENTATIVE
    210539662   2019-10-17  PRODUCT A                           PREVENTATIVE
    210539662   2019-11-19  PRODUCT B                           TREATMENT   
    210539662   2019-12-20  PRODUCT B                           TREATMENT

I would like to pull a list of all member numbers and the first date they switched from preventative products to treatment products.

Please be aware I am using Sybase IQ and I don't believe its possible run ordered sub-queries.

Solution

  • I would like to pull a list of all member numbers and the first date they switched from preventative products to treatment products.

    Use lag() and group by:

    select member,
       min(case when prev_tot = 'PREVENTATIVE' and tot = 'TREATMENT'
                then date
           end) as first_prev_to_treat_date
from (select t.*,
             lag(tot) over (partition by member order by date) as prev_tot
      from t
     )  t
group by member;