Calculate the Difference of Purchase Amounts Between Transactions

Problem

It is interesting to compare purchase amount between the current transaction and the previous one. For example, the difference between a customer's current purchase amount and previous purchase amount may indicate frauds or may be used to predict customer's future spending trend. Taking the following table as an example, how to do calculate the difference in order amount between the current transaction and previous transaction for each customer_id?

SQL> select CUSTOMER_ID, order_id, ORDER_TOTAL, 
ORDER_TIMESTAMP from DEMO_ORDERS 
order by CUSTOMER_ID, ORDER_TIMESTAMP desc;

CUSTOMER_ID   ORDER_ID ORDER_TOTAL ORDER_TIME
----------- ---------- ----------- ----------
          1          1        1200 2013-08-15
          2          2         599 2013-08-10
          2          3        1999 2013-08-05
          3          4         750 2013-07-31
          3          5          40 2013-07-26
          4          6         250 2013-07-21
          5          7        3800 2013-07-16
          6          8          40 2013-07-11
          6          9         450 2013-07-06
          7         10         500 2013-07-01

10 rows selected.

Solution

We use Oracle analytic function lag as shown below. "lag(ORDER_TOTAL,1)" means getting the 1 previous row's order_total. "order by ORDER_TIMESTAMP" indicates that the "previous" is determined by order_timestamp. "partition by CUSTOMER_ID" specifies that the order is done independently by customer_id.

SQL> select CUSTOMER_ID, order_id, ORDER_TOTAL, ORDER_TIMESTAMP, ORDER_TOTAL-lag(ORDER_TOTAL,1) over(partition by CUSTOMER_ID 
order by ORDER_TIMESTAMP) delta 
from DEMO_ORDERS 
order by CUSTOMER_ID, ORDER_TIMESTAMP desc;

CUSTOMER_ID   ORDER_ID ORDER_TOTAL ORDER_TIME      DELTA
----------- ---------- ----------- ---------- ----------
          1          1        1200 2013-08-15
          2          2         599 2013-08-10      -1400
          2          3        1999 2013-08-05
          3          4         750 2013-07-31        710
          3          5          40 2013-07-26
          4          6         250 2013-07-21
          5          7        3800 2013-07-16
          6          8          40 2013-07-11       -410
          6          9         450 2013-07-06
          7         10         500 2013-07-01

10 rows selected.

As we see, customer 2 has a big drop in order_total (-1400) on 2013-08-10. While customer 3 has a jump in order_total (710) on 2013-07-31. Most of transactions in the above table do not have previous transactions.