Identify purchasing patterns SQL

Question

I wish to identify members who bought a sequence of products from my store and save their details for further analysis.

I am interested in identifying members who bought apples AND whether they previously bought either pears OR oranges OR grapes OR melon.

My transactional data looks as follows:

From the data above member 1 bought apples (4/4/2020) but did not buy one or many of the key products (pears OR oranges OR grapes OR melon) before this. However, member 2 also bought apples but prior to this they also bought pears (02/22/2020) therefore they are a member of interest.

Ideally the output would just be a column of MBR_ID and a column titled interest_y_n with y if the condition is matched or no if they do not match.

I would really appreciate any direction on how to solve this problem.

I am using Netezza as the database platform if this helps.

Answer 1

You can do using CTE as well. Here is the demo.

with maxDate as
(select
    mbr_id,
    max(purchase_date) as mxDate
from test
where product = 'apples'
group by
    mbr_id
),

minDate as
(
  select
    mbr_id,
    min(purchase_date) as mnDate
  from test
  group by
    mbr_id
),

inter_y as
(
  select
      t.mbr_id,
      'y' as interest_y_n
  from test t
  join maxDate mx
  on t.mbr_id = mx.mbr_id

  join minDate mn
  on t.mbr_id = mn.mbr_id 

  where purchase_date between mnDate and mxDate
  and product in ('pears', 'oranges', 'grapes', 'melon')
)

select
    distinct mbr_id,
    'n' as interest_y_n
from test t
where not exists (select mbr_id from inter_y iy where t.mbr_id = iy.mbr_id)

union all

select *
from inter_y

Output:

*----------------------*
| mbr_id  interest_y_n |
*----------------------*
|     1        n       |
|     2        y       |
*----------------------*