The "Six SQL patterns I use to catch transaction fraud" thread here was great, and every one of those patterns is a rule: you know what fraud looks like, you write the WHERE clause. Here's a 7th that doesn't need the pattern up front, and the case where it falls flat.
Toy dataset: 123 transactions, 40 accounts. Each account makes three ordinary payments ($20–$199, daytime, domestic). ACC-33 also makes payments of $2,840, $3,120 and $2,975 at 3am, cross-border. Hidden among the ordinary accounts is a 5-account ring (ACC-11 to ACC-15). Nothing in the table marks them.
Train an anomaly model, no labels
Isolation forest, no target column. It learns what normal rows look like and scores distance from that.
CREATE EXPERIMENT tx_exp AS
SELECT amount, hour_of_day, cross_border FROM tx
WITH (task_type = 'anomaly_detection',
algorithms = ['isolation_forest'],
max_trials = 5, random_seed = 42);
DEPLOY MODEL tx_model FROM EXPERIMENT tx_exp;
Score every row in a SELECT
SELECT account_id, amount, hour_of_day, cross_border,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
ORDER BY score DESC
LIMIT 10;
ACC-33's payments come out on top at 0.8167, 0.8167 and 0.8064. Then a straight drop to 0.5267, with the next normal rows between 0.5100 and 0.5181. Higher means more unusual; it's a ranking, not a probability. It's just an expression, so it works in WHERE, GROUP BY, views.
Features must be passed in training order. Swap two and you get a feature mismatch error instead of quietly wrong scores.
Where it falls flat: the ring
SELECT account_id, amount, hour_of_day,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
WHERE account_id IN ('ACC-11','ACC-12','ACC-13','ACC-14','ACC-15')
ORDER BY score DESC;
15 rows, from 0.4453 to 0.5081. Same as honest customers. The model isn't wrong: each payment is ordinary. A ring isn't a property of any single row.
What gives it away is shared identifiers: ACC-11/12 share a phone, 12/13 a card, 13/14 a phone, 14/15 a card. ACC-13 has a confirmed chargeback. Walking shared devices and cards 2–4 hops from it finds the other four:
| linked |
via |
| ACC-14 |
ACC-13 → DEV-778 → ACC-14 |
| ACC-12 |
ACC-13 → CARD-4242 → ACC-12 |
| ACC-15 |
ACC-13 → DEV-778 → ACC-14 → CARD-9001 → ACC-15 |
| ACC-11 |
ACC-13 → CARD-4242 → ACC-12 → DEV-777 → ACC-11 |
In most databases that's a recursive CTE over account–device and account–card link tables. We used a graph query in the same engine:
MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);
An honest household (ACC-21/22 share a tablet) isn't returned. Sharing a device isn't fraud; being connected to a chargeback is.
One review queue, both signals
SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
SELECT account_id, MAX(score) AS max_anomaly
FROM (SELECT account_id,
ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx) t
GROUP BY account_id
) s
LEFT JOIN ring_members r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;
| account |
max_anomaly |
linked_via |
| ACC-33 |
0.82 |
(model) |
| ACC-15 |
0.51 |
DEV-778 > CARD-9001 |
| ACC-12 |
0.51 |
CARD-4242 |
| ACC-13 |
0.49 |
chargeback |
| ACC-14 |
0.48 |
DEV-778 |
| ACC-11 |
0.48 |
CARD-4242 > DEV-777 |
Five of six would never cross 0.7. The model found one fraudster; the identifier walk found five.
Takeaways
- Unsupervised scoring is a decent 7th pattern for the lone weird account you didn't write a rule for.
- It's blind to coordinated fraud where every row looks normal.
- Synthetic data built to show the gap. Real scores will be noisier; use them to rank for review, not as verdicts.
Anyone here run anomaly scoring in-database, or do you export to Python and join scores back?
Disclosure: I work on SynapCores, where ANOMALY_SCORE and the graph query come from. The rule-based patterns from the original thread still do most of the work.
