Add new lookup column - customer return within 30 days

Question

Context of the problem: When a customer purchased items at a grocery store on a given day, it is recorded as a row in the transaction table, I want to add a "lookup" column in the select statement to check if same customer have another transaction within 30 days. The "30 days" is current transaction date + 30, the new lookup column will add to the end of select *.

db.transaction:Customer_ID, Transaction_ID, Transaction Date

Query returns: Customer_ID, Transaction_ID, Transaction Date, **Return within 30 days (Yes/No)**

I thought the new lookup column could be a case expression but I don't know how to get the next available transaction date for the same customer and comparing with the transaction date in the current row.

case 
   when datediff(day, transaction_date,  "next available date")<=30 then 'Yes
   else 'No'
end as 'return_within_30_days'

Thanks for any help! (I am using SQL Server)

Answer 1

Use LEAD to get a customer's next transaction. Your "next available date" translates to

LEAD (transaction_date)
  OVER (PARTITION BY customer_id ORDER BY transaction_date)

The complete query:

select
  customer_id,
  transaction_id,
  transaction_date,
  case 
   when datediff(day, 
                 transaction_date,
                 lead (transaction_date)
                   over (partition by customer_id order by transaction_date)
                ) <= 30
   then 'YES'
   else 'NO'
  end as return_within_30_days
from mytable
order by customer_id, transaction_date;