12c Fraud detection using pattern matching. (MATCH_RECOGNIZE)

Just wanted to show different real scenarios  where 12c pattern matching could be used.  

This feature could be handy in running one of several scenarios of Credit Card frauds.

One common use case is when a credit card gets stolen and the thief first purchases a small item such as candy or gas to make sure the card works and then purchases a large ticket item as quickly as possible before suspicions arise in a 15 minute span. 

To run this with 12c match_recognize, the query will be as show below.

select * from transaction
MATCH_RECOGNIZE
(
  partition by seller_name order by tx_time
  MEASURES
         to_char(FIRST(low.tx_time),'DD-MON-YY HH24:MI') AS first_low_time,
        FIRST(low.tx_amount) first_tx_value,
      --  last(low.tx_amount) last_low_amount,
        (SPIKE.tx_amount) big_tx,
        to_char(spike.tx_time,'DD-MON-YY HH24:MI') AS big_tx_time,
        match_number() as match_num
        ONE ROW PER MATCH
        AFTER MATCH skip to next row
        PATTERN (STRT LOW* SPIKE)
        DEFINE
        LOW as low.tx_amount < 50,
        SPIKE as spike.tx_amount > 500 and (spike.tx_time - low.tx_time)*1440 < 15
) ;

SE FIRST_LOW_TIME   FIRST_TX_VALUE      BIG_TX       BIG_TX_TIME            MATCH_NUM

-- ---------------- --------------  ----------   ------------------------  ----------
aa 22-OCT-13 19:25              32         503       22-OCT-13 19:28               1

aa 22-OCT-13 19:26              23         503       22-OCT-13 19:28               2

aa 22-OCT-13 19:27              12         503       22-OCT-13 19:28               3

This is a challenge to build using 11g style analytical functions.
By simple way of defining patters and then searching for one or more occurrence of them, we build a complex query. If we to add additional condition to the above query, we define the condition and do patter matching.

This patter matching within database is also called mapreduce technique also.

Below is the sample data created to run this query.

-- drop table transaction purge;

create table transaction ( tx_amount number, zip number, tx_time date, seller_name varchar2(32)) ;
insert into transaction values (2, 75028,sysdate +1/1440, 'aa');
insert into transaction values (12, 75028,sysdate +2/1440, 'aa');
insert into transaction values (2, 75028,sysdate +3/1440, 'aa');
insert into transaction values (25, 75028,sysdate +4/1440, 'aa');
insert into transaction values (55, 75028,sysdate +4/1440, 'aa');
insert into transaction values (5, 75028,sysdate +5/1440, 'aa');
insert into transaction values (10, 75028,sysdate +6/1440, 'aa');
insert into transaction values (2, 75028,sysdate +7/1440, 'aa');
insert into transaction values (432, 75028,sysdate +8/1440, 'aa');
insert into transaction values (2, 75028,sysdate +9/1440, 'aa');
insert into transaction values (43, 75028,sysdate +10/1440, 'aa');
insert into transaction values (2, 75028,sysdate +11/1440, 'aa');
insert into transaction values (35, 75028,sysdate +12/1440, 'aa');
insert into transaction values (35, 75028,sysdate +13/1440, 'aa');
insert into transaction values (35, 75028,sysdate +15/1440, 'aa');
insert into transaction values (88, 75028,sysdate +16/1440, 'aa');
insert into transaction values (52, 75028,sysdate +17/1440, 'aa');
insert into transaction values (32, 75028,sysdate +18/1440, 'aa');
insert into transaction values (23, 75028,sysdate +19/1440, 'aa');
insert into transaction values (12, 75028,sysdate +20/1440, 'aa');
insert into transaction values (503, 75028,sysdate +21/1440, 'aa');
insert into transaction values (66, 75028,sysdate +21/1440, 'aa');
insert into transaction values (45, 75028,sysdate +22/1440, 'aa');
insert into transaction values (45, 75028,sysdate +23/1440, 'aa');
insert into transaction values (23, 75028,sysdate +21/1440, 'aa');
insert into transaction values (66, 75028,sysdate +21/1440, 'aa');
insert into transaction values (509, 75028,sysdate +40/1440, 'aa');
insert into transaction values (45, 75028,sysdate +23/1440, 'aa');

commit;