I would really appreciate some help with a query I'm struggling with.

I have created a number of joins in order to create a table of what products each customer bought on each date. Each transaction is on each row.

I want to find those customers which have both purchased product A and B where product B was purchased after Product A. I would then like the output to show only the Customer IDs that meet this criteria.

So in the example below customer ID jones and blogs should appear on my output because they both purchased product ID A and B where A was purchased first. Lane wont make the list because they have not purchased the stated products or in the correct order. So far I have tried using the where and having clause as well as group by to no avail.

Sample Data

Purchase Table

Expected output

I hope that makes sense!

CodePudding user response：

select distinct a.CustomerId
from myTable a 
where a.ProductId = 'A' and 
 exists (select * from myTable b 
            where a.CustomerId = b.CustomerId  
              and b.ProductId = 'B' 
              and a.TransactionDate < b.TransactionDate);

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

SELECT customer_id
FROM   (SELECT * FROM purchase WHERE product_id IN ('A', 'B'))
MATCH_RECOGNIZE (
  PARTITION BY customer_id
  ORDER     BY transaction_date
  PATTERN ( ^ a  b )
  DEFINE
    a AS product_id = 'A',
    b AS product_id = 'B'
);

Which, for the sample data:

CREATE TABLE purchase (customer_ID, Product_ID, Transaction_Date) AS
SELECT 'jones', 'A', DATE '2020-06-20' FROM DUAL UNION ALL
SELECT 'jones', 'B', DATE '2020-06-26' FROM DUAL UNION ALL
SELECT 'lane',  'C', DATE '2020-06-15' FROM DUAL UNION ALL
SELECT 'lane',  'A', DATE '2020-06-14' FROM DUAL UNION ALL
SELECT 'jones', 'C', DATE '2020-06-21' FROM DUAL UNION ALL
SELECT 'blogs', 'A', DATE '2020-06-05' FROM DUAL UNION ALL
SELECT 'blogs', 'B', DATE '2020-06-21' FROM DUAL;

Outputs:

CUSTOMER_ID
blogs
jones

db<>fiddle here