This scenario is an OFFLINE (post transactional) scenario.
SELECT
case
when fr.level = 'LOW' then abs(t.amount) > 25000
when fr.level = 'MEDIUM' then abs(t.amount) > 10000
when fr.level = 'HIGH' then abs(t.amount) > 5000
else false
end,
abs(t.amount)
FROM transaction t
JOIN final_risk fr ON t.person_id = fr.person_id
WHERE t.id = $transactionId
and fr.customer_id = $organisationId
and fr.end_time is null
We can adjust threshold for different customer segments based on AML risk levels (Salv risk assessment tool) when joining final_risk table to transaction table. final_risk stores the values of system calculated risk levels. It does not take into account manual risk override.
To account only for active risk level it is important to add where statement final_risk.end_time is null.
Where statement final_risk.customer_id = $organisationId is necessary to enhance performance
There is also table called active_final_risk which has single entry per person and takes also into account manual risk override.
The scenario above using active_final_risk would be:
SELECT
case
when fr.level = 'LOW' then abs(t.amount) > 25000
when fr.level = 'MEDIUM' then abs(t.amount) > 10000
when fr.level = 'HIGH' then abs(t.amount) > 5000
else false
end,
abs(t.amount)
FROM transaction t
JOIN active_final_risk ON t.person_id = active_final_risk.person_id
WHERE t.id = $transactionId
and active_final_risk.organisation_id = $organisationId