Breakdown of a TM scenario: ‘INDIVIDUAL: Large Movement of Funds Coming into and out of an Account on a Single Day’

This scenario is included in the demo account under Analyst Toolbox → Transaction scenarios

This is an post-event scenario, which means it’s triggered asynchronously and alert is generated some time after the data is sent to Salv (either via API call or batch upload) and if there is a hit.

select 
     sum(case when past.direction = 'INCOMING' then past.amount end) >= 1000 
     and 
sum(case when past.direction = 'OUTGOING' then past.amount end)::float 
 / sum(case when past.direction = 'INCOMING' then past.amount end) BETWEEN 0.9 and 1.1, 
   sum(case when past.direction = 'INCOMING' then past.amount end) as IN, 
   sum(case when past.direction = 'OUTGOING' then past.amount end) as OUT 
from 
   ( 
    select 
      person_id, 
      timestamp 
    from 
      transaction 
    where id = $transactionId 
    ) new 
join transaction past on 
    past.person_id = new.person_id 
join person p on 
    p.id = new.person_id 
    and p.customer_id = $organisationId 
where 
    past.timestamp between new.timestamp-'1 days'::interval and new.timestamp 
    and past.type = 'INDIVIDUAL'

Let’s start by looking at the subquery called new (the query that is executed first by the database engine). And where the currently sent transaction person_id and timestamp is selected.

select 
      person_id, 
      timestamp 
   from 
      transaction 
   where 
      id = $transactionId

This query uses a special variable $transactionId - this is always in the scope of post-event and real-time TM scenario and references the ID of the currently processed transaction (the triggering part). We are returning person_id and timestamp in the select clause.

That is because we want to look at other transactions that are made by the same person within a timeframe.

Now, let’s look at the transactions of the same person within a timeframe. The timeframe being 1 day.

join transaction past on 
    past.person_id = new.person_id 
join person p on 
    p.id = new.person_id 
    and p.customer_id = $organisationId 
where 
    past.timestamp between new.timestamp - '1 days'::interval and new.timestamp 
    and p.type = 'INDIVIDUAL'

We are joining the transactions of the same person, which now can be accessed via past. and we are also joining the person table which can be accessed via p. for looking at person details. In the where clause we specify that we only want to look at

transactions that are done between the time of the current transaction (the one called new with id = $transactionId) and one day before
that we only want to look at persons with type INDIVIDUAL

Finally we arrive at the select clause which interacts with the Salv system. And returns a boolean (true or false) in the bold section below:

select 
     sum(case when past.direction = 'INCOMING' then past.amount end) >= 1000 
     and 
         sum(case when past.direction = 'OUTGOING' then past.amount end)::float 
         / sum(case when past.direction = 'INCOMING' then past.amount end) BETWEEN 0.9 and 1.1, 
     sum(case when past.direction = 'INCOMING' then past.amount end) as IN, 
     sum(case when past.direction = 'OUTGOING' then past.amount end) as OUT

The boolean evaluates to true if

all the rows with past.direction = 'INCOMING' have the amount summed >= 1000
and the ratio of summed amounts outgoing/incoming is between 0.9 and 1.1

Figure showing what the select query sees, when the rule scenario hits.

We also return fields IN and OUT because we have specified the following details:

Breakdown of a TM scenario: aggregations: ‘Current transaction is sent to a recipient account that has received a total of 20000 amount in the last 30 days from at least 20 other senders’

Breakdown of a TM scenario: jsonb and hstore attributes in queries

Scenarios with accessing previous alerts

Best practices for writing and optimising Monitoring scenarios

Parameters in Scenarios