If a product was ordered earlier

Question

Table :

order_date.     order_id.    customer_id.    product id
2020-01-01      O1.          C1.             P1
2020-01-01      O1.          C1.             P2
2020-01-01      O1.          C1.             P3
2020-01-10      O2.          C1.             P2
2020-01-10      O2.          C1.             P3
2020-01-10      O2.          C1.             P4
2020-01-10      O2.          C1.             P5

I used the code as below, but for huge volume of data (say 10M rows), it times out. Any better way to improve the performance of this query?

select a.order_date, a.customer_id, a.order_id, a.product_id, case when b.product_id = a.product_id then 'Y' else 'N' end as prev_purchase_sts
from table a
left join table b on a.customer_id = b.customer_id and a.order_date > b.order_date

score 1 · Accepted Answer · answered Aug 25 '21 at 19:01

If you want to check that a customer has ordered the same product previously, use EXISTS:

SELECT a.*, 
       CASE 
         WHEN EXISTS (
                SELECT 1 
                FROM table b 
                WHERE a.customer_id = b.customer_id AND a.product_id = b.product_id AND a.order_date > b.order_date
              ) THEN 'Y' 
         ELSE 'N' 
       END AS prev_purchase_sts
FROM table a;

or, LAG() window function:

SELECT *, 
       CASE 
         WHEN LAG(order_date) OVER (PARTITION BY customer_id, product_id ORDER BY order_date) IS NOT NULL THEN 'Y' 
         ELSE 'N' 
       END AS prev_purchase_sts
FROM table;

or, ROW_NUMBER() window function:

SELECT *, 
       CASE 
         WHEN ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY order_date) > 1 THEN 'Y' 
         ELSE 'N' 
       END AS prev_purchase_sts
FROM table;

If a product was ordered earlier

1 Answers1