SQL: How do I filter rows individually using a variable?

Question

I feel like an example better illustrates my problem.

What I'm trying to do is select each purchase for every client one year back from their most recent purchase date. So if Client A's most recent purchase was January 5, 2017, then I want their purchases from Jan 5 2016 until Jan 5 2017. However, if someones most recent purchase was Feb 2 2017, I only want their purchases from Feb 2 2016 to Feb 2 2017.

I've been able to do this manually entering dates but I cant do this because I want to implement this data into an automated model

I don't understand. If the most recent was Jan 1, why does the range use Jan 5? — Barmar
Your feelings are spot on. Your actions don't live up to expectations. See meta.stackoverflow.com/questions/333952/… — Strawberry

Barmar Barmar · Accepted Answer · 2017-06-23T22:40:55

Join with a subquery that gets the most recent time for each client, then use that in the joining condition.

SELECT p.*
FROM purchases AS p
JOIN (SELECT client_id, MAX(purchase_data) AS maxdate
      FROM purchases
      GROUP BY client_id) AS m
ON p.client_id = m.client_id 
    AND p.purchase_date BETWEEN DATE_SUB(m.maxdate, INTERVAL 1 YEAR) AND m.maxdate

SQL: How do I filter rows individually using a variable?

1 Answers