Rules-based fraud detection in SQL

The problem

Every fraud team runs the same trade-off. Loosen the rules and you catch more fraud, but analysts drown in false alerts. Tighten them and fraud gets through. This project builds a ruleset and then measures that trade-off directly.

The data

No public dataset matched NZ retail banking closely enough, so I wrote a simulator in Python. It generates 60 days of activity for 6,000 customers, with spending scaled to income, then injects four fraud typologies on top.

Normal behaviour includes the noise rules have to live with: a quarter of customers pay a legitimate new payee, and 5% switch to a new phone.

The rules

Every feature is built with SQL window functions that only look backwards, so no rule ever uses information it wouldn't have at the moment of payment. The fraud labels are used only for scoring. Here's the account-takeover rule:

SELECT tx_id, 'R1_new_device_new_payee_large' AS rule
FROM features
WHERE direction = 'debit' AND channel = 'transfer'
  AND first_on_device          -- first time this customer used this device
  AND first_to_payee           -- first payment to this payee
  AND amount > coalesce(max_prior_transfer, 0);  -- bigger than any transfer in 60 days

*Perfect precision here reflects the simulator, not the real world. Real households share devices and real merchants run small test charges, so in production these rules need allow-lists and tuning.

What I changed along the way

A warm-up window fixed the worst rule

On the first run, R1 fired 1,519 times at 3.8% precision. Every customer's first-ever online payment looked like "new device plus new payee". Adding a 14-day baseline before alerting took R1 to 80 alerts at 72.5% precision, with no loss in cases caught. Banks face the same cold-start problem with every new customer.

Tuning the noisiest rule

R3 produces most of the alert volume, so I swept its threshold and scored the whole ruleset at each setting.

Going from 1x to 2x halves the alert load and loses one case. Past 2x, every step costs real fraud. Where to sit is a business decision about analyst capacity and risk appetite: a smaller team might choose 3x and catch 217 cases at 27 alerts a day.

What it missed, and why

Six cases got through. Each one points at a real weakness.

• Split payments. One scam victim's first payment to the scammer was under 2x their baseline; the second was 2.7x. R3 only checks the first payment to a payee. Fix: compare the 7-day running total to a new payee against the baseline.
• No history. One victim had no prior transfers, so R3 had nothing to compare against. Fix: a peer-group baseline for thin-history customers.
• Ordinary-looking scams. Three scam payments sat inside the customer's normal range. Rules alone won't catch these. They need Confirmation of Payee, scam warnings at the point of payment, or a model using more signals.
• Quiet takeover. One takeover happened mid-afternoon for a small amount. A new device plus a new payee is risky at any size, so a lower-priority alert tier would catch it.

Run it yourself

pip install duckdb pandas numpy matplotlib
python simulate.py        # writes ~585k labelled transactions
python evaluate.py        # runs rules.sql and scores every rule
python tradeoff_chart.py  # redraws the chart

Runs in under a minute on a laptop, with a fixed random seed so results reproduce exactly. Download the code (zip).