1

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.

Edit: adding attempted code below:

select
   s.CUST_ACCT_ID
  ,s.PRODUCT
  ,s.BUS_MO  
  ,s2.BUS_MO

from INS_REF001_SALES_SUM_R48 s

left outer join INS_REF001_SALES_SUM_R48 s2
on s.CUST_ACCT_ID = s2.CUST_ACCT_ID
and s.PRODUCT = s2.PRODUCT
and s.BUS_MO < s2.BUS_MO
and s.BUS_MO >= dateadd(mm, datediff(mm, 0, s2.BUS_MO) - 12, 0)

inner join INS_REF006_ACCT a
on a.CUST_ACCT_ID = s.CUST_ACCT_ID
and a.CUST_ACCT_ID = s2.CUST_ACCT_ID

where s.BUS_MO between '11/01/2016' and '10/31/2018'

Thanks! Scott

  • Good question. Usually showing table structure and example desired output will complement well with a well written description (like you've provided). – danblack Nov 8 '18 at 21:22
  • note MySQL dates are never represented in US wonky date format :-) only YYYY-MM-DD – danblack Nov 8 '18 at 22:20
0

You can accomplish this with a self join:

SELECT a.customer_id, a.product_id, a.purchase_date, MAX(b.purchase_date) IS NOT NULL AS previous_12_months
FROM products a
LEFT JOIN products b
  ON a.product_id = b.product_id AND
     a.customer_id = b.customer_id AND
     b.purchase_date < a.purchase_date AND
     b.purchase_date >= a.purchase_date - INTERVAL 12 MONTH
GROUP BY a.customer_id
  • Thanks, danblack! I'll play around with your solution. I updated my question with a a sample of my code. Ideally, I'd like to return a "1" if there is a sale 12 months prior to purchase, and 0 if none. – Scott Mohler Nov 8 '18 at 22:07
  • should be closer, just need to join to customer data. – danblack Nov 8 '18 at 22:21
  • This is brilliant! Thank you so much, danblack! – Scott Mohler Nov 8 '18 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.