I'm new, and not even sure how to ask this question, but here goes:
For each customer/product purchase, I need to determine with SQL whether the same customer purchased that product in the 12 months prior to this purchase. So, if purchase is on 9/1/2018, I need to know if the customer purchased the same product in previous 12 months.
I've tried combinations of RANK() OVER(PARTITION BY but haven't really accomplished what I need to.
You can accomplish this with a self join:
SELECT a.customer_id, a.product_id, a.purchase_date, b.purchase_date FROM products a JOIN products b ON a.product_id = b.product_id AND a.customer_id = b.customer_id AND a.purchase_date < b.purchase_date AND a.purchase_date >= b.purchase_date - INTERVAL 12 MONTH