This scenario is included in the demo account under Analyst Toolbox → Transaction scenarios.
select
count(distinct pa.person_id)>= 20
and sum(value) >= 20000 ,
sum(value),
count(distinct pa.person_id )
from
(
select
receiver_account,
timestamp
from
transaction
where
id = $transactionId
and direction = 'OUTGOING'
) new
join person_aggregate pa on
pa.receiver = sub.receiver_account
and pa.organisation_id = $organisationId
where
pa.aggregate_date between sub.timestamp-'30 days'::interval and sub.timestamp
and pa.aggregate_type = 'TRANSACTION_TO_RECEIVER_ACCOUNT'
and pa.aggregate_operation = 'SUM'
Let’s start from the subquery called new, where it’s checked whether the currently monitored transaction direction is OUTGOING. If it is - then the receiver_account and timestamp are selected.
select
receiver_account,
timestamp
from
transaction
where
id = $transactionId
and direction = 'OUTGOING'
The special variable $transactionId is needed to select the currently monitored transaction
For optimising the performance of the query an aggregate table called person_aggregate is used for getting the sum of amounts sent to the receiver account in last 30 days.
join person_aggregate pa on
pa.receiver = sub.receiver_account
and pa.organisation_id = $organisationId
where
pa.aggregate_date between sub.timestamp-'30 days'::interval and sub.timestamp
and pa.aggregate_type = 'TRANSACTION_TO_RECEIVER_ACCOUNT'
and pa.aggregate_operation = 'SUM'
Finally the conditions needed for an alert to appear are evaluated and necessary details (the sum and count) are returned for alert details.
select
count(distinct pa.person_id)>= 20
and sum(value) >= 20000,
sum(value),
count(distinct pa.person_id)
More than or equal to 20 unique receiver person_id-s and the sum of all transactions received more than or equal to 2000.