Purchased in Previous 12 Months from Date of Purchase - SQL

by Scott Mohler   Last Updated November 08, 2018 22:06 PM

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.

Thanks! Scott

Tags : mysql

Answers 1

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
November 08, 2018 21:19 PM

Related Questions

The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM

getting innodb buffer usage

Updated August 24, 2018 15:06 PM