GoKawiilSix SQL patterns I use to catch transaction fraud May 12, 2026
Quick disclaimer: I do data work on a program-integrity team. Examples below use generic transaction tables and made-up scenarios. Nothing here comes from anything I’ve actually worked on or seen. Views are mine, not my employer’s.
Fraud detection in transaction data is mostly SQL. Not machine learning, not graph databases, not whatever Gartner is hyping this year. SQL, run against the right tables, with the right joins, looking for the right shapes.
I work mostly with government-funded benefit programs, but the patterns below port over to anything with a transactions table: credit cards, healthcare claims, e-commerce, point-of-sale. If money moves and gets logged, these queries will find weird things in the log.
Six patterns. Roughly in the order I’d build them out on a new dataset.
1. Velocity
The simplest one. Someone with a stolen card wants to drain it before the holder notices. So they hit the card fast.
SELECT cardholder_id, date_trunc( 'hour' , timestamp ) AS hour_bucket, count ( * ) AS tx_count, min ( timestamp ) AS first_tx, max ( timestamp ) AS last_tx FROM transactions WHERE timestamp >= current_date - INTERVAL '30 days' GROUP BY 1 , 2 HAVING count ( * ) > 10 ;
Tune two knobs: the window size and the count threshold. I usually run a 1-minute, 5-minute, and 1-hour version in parallel and compare. Different fraud shows up at different scales — a card-testing ring hits a server in seconds; a benefits-trafficking ring might take an afternoon.
A few cardholders will legitimately blow past the threshold. Route operators servicing vending machines. People reloading prepaid cards in bulk. Your false positives. Worth keeping a whitelist after the first pass.
... continue reading