Recency, Frequency, Monetary (RFM) Analysis: Part 3

Frequency measures how many transactions happen within certain period of time. Usually, the more frequently a customer responds to the direct mailing offerings in the past, the more likely she will make additional purchases. In the case of credit card fraud, the fraudsters tend to make more frequent purchases than normal consumers do. Frequency variable can be calculated using SQL analytic function such as count() over().

Again we use the following credit card transactions as examples.
CARD_NUMBER     TXN_ID TXN_DATE   TXN_TYPE        TXN_AMT
----------- ---------- ---------- ------------ ----------
     123457          1 0304:11:00 Grocery           85.35
     123457          2 0304:17:30 Electronic      2100.75
     123457          3 0305:08:25 Gas Station           1
     123457          4 0305:08:36 Electronic       1435.2
     123457          5 0305:11:23 Grocery            55.3

The following query calculate the number of transactions in the past 6 (0.25 day) and12 hours (0.5 day) before the current transaction.
select 
card_number, 
to_char(TXN_DATE,'YYYYMMDD:HH24:MI') txn_date, 
count(1) over(partition by CARD_NUMBER order by txn_date range between 0.25 preceding and 0.0000001 preceding) num_in_6h,
count(1) over(partition by CARD_NUMBER order by txn_date range between 0.5 preceding and 0.0000001 preceding) num_in_12h
from TBL_CARD_TXN   a
order by CARD_NUMBER, txn_date;

CARD_NUMBER TXN_DATE        NUM_IN_6H NUM_IN_12H

---------- -------------- ---------- ----------

    123457 20120304:11:00          0          0

    123457 20120304:17:30          0          1

    123457 20120305:08:25          0          0

    123457 20120305:08:36          1          1

    123457 20120305:11:23          2          2