Six production SQL patterns for fraud detection: velocity, impossible travel, amount anomalies, suspicious merchants, off-hours activity, and window function primitives.
Key Takeaways
Velocity queries run at 1-minute, 5-minute, and 1-hour windows in parallel; card-testing rings hit in seconds, trafficking rings take hours.
Impossible travel uses haversine distance with a 600 mph threshold; anything faster than a commercial jet flags as cloned card activity.
Amount anomalies target round-dollar card tests ($1, $5, $10) and just-below-threshold buys ($99.99, $499.99) tied to ID-check and ATM-cap rules.
Merchant spike detection compares each merchant against its own 168-hour rolling baseline rather than static thresholds to avoid false positives at high-volume locations.
Window function pre-computation materializes time-since-last, rolling 24h totals, and tx-of-day columns so new fraud rules become simple WHERE filters, cutting iteration from weeks to hours.
