SQL Query for Comparing Customer Order Amounts

Question

I'm looking to create a query that could compare a customer's latest order purchase amount to the previous order amount of the customer's last purchase. Please see example data screenshot below:

Ideally I'd like the query to look for these things in the results:

• Total amount from previous order before most recent order date (in this case 9/6/18 would be most recent and 2/2/17 would be the last purchase)

• Difference in amount between most recent order and last order amount ($2000-$25 = $1975)

• Create a condition in the query to look for customers whose most recent order attempt is 1000 > last purchase amount and the age of the customer's account age is > than 60 days

Note: These conditions for the last bullet could be modified as needed (customer's account age is > 90 days, different in order amount is $500, etc)

Thank you for the assistance!

Answer 1

For 2012 onward you can use LAG

declare @amount decimal(16,2) = 1000
declare @days int = 60

select 
   *
   ,TotalWithPrevious = [Order Amount] + lag([Order Amount]) over (partition by UserID order by [Order Date] desc)
   ,DifferenceofPrevious = [Order Amount] - lag([Order Amount]) over (partition by UserID order by [Order Date] desc)
   ,CheckCondition = case 
                      when [Order Amount] - lag([Order Amount]) over (partition by UserID order by [Order Date] desc) >= @amount
                           and datediff(day,[Order Date],lag([Order Date]) over (partition by UserID order by [Order Date] desc)) >= @days 
                           then 'True' 
                           else 'False' 
                      end
from YourTable