CODEWITHSUNDEEP
mysqlsql
crowsnest
crowsnest

Reputation: 59

MYSQL Query That Outputs "Prior Transaction Date" Per Customer Transaction

Let's say I have a table that reflects all of the individual purchases customers have made to date (see image below for the output i'm envisioning)

How would I write a query in MYSQL that returned these 2 columns, +:

  1. A column that reflected the purchase date of that customer's purchase made directly prior (and in the case of no prior purchase, a null value)

  2. A column that output a value of "1" for every difference in the two date columns that are greater than 70 days, a value of "0" for differences that are less than 70 days, and a null value for those that don't have a "prior purchase".

I have been working on this for days and I have only gotten it to work when I "GROUP BY" the customer ID's (using a self join that requires one date to be less than the other). I have no idea how i'd do it at the transaction level.

enter image description here

Upvotes: 1

Views: 339

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You can use a correlated subquery. Here is how you get the previous date:

select p.*,
       (select p2.purchase_date
        from purchases p2
        where p2.customerid = p.customerid and
              p2.purchase_date < p.purchase_date
        order by p2.purchase_date desc
        limit 1
       ) as prev_purchase_date
from purchases p;

You can use this as a subquery and then do the calculation for the final column using prev_purchase_date.

Upvotes: 2

Related Questions