Add new lookup column - customer return within 30 days-CodePudding

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)

CodePudding user response：

Use LEAD to a customer's the 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;