This is a simple High-Value transaction scenario where the threshold depends on the person status.
SELECT
CASE
WHEN ps.status = 'REVIEWED' THEN abs(t.amount) > 50000
WHEN ps.status = 'NEW' THEN abs(t.amount) > 10000
WHEN ps.status = 'MONITORING' THEN abs(t.amount) > 1000
WHEN ps.status = 'SUSPENDED' THEN TRUE
ELSE FALSE
END,
abs(t.amount)
FROM
TRANSACTION t
JOIN person_status ps ON
ps.personid = t.person_id
WHERE
t.id = $transactionId
AND ps.organisation_id = $organisationId
AND ps.end_time IS NULL
We can check for different customer statuses when joining person_status table to transaction table and adjust thresholds accordingly.
To account only for active customer status it is important to add person_status.end_time is null statement to where clause.
The statement person_status.organisation_id = $organisationId in where clause is necessary to enhance performance.