Six SQL Patterns for Detecting Transaction Fraud

This technical article outlines six fundamental SQL patterns used by data analysts to detect fraud in transaction logs, emphasizing that structured query language remains more effective than hyped machine learning or graph database solutions for many integrity tasks. The author, working in program integrity for government benefits, demonstrates how these methods apply universally to credit cards, healthcare claims, and e-commerce. The text details the first two patterns: Velocity and Impossible Travel. Velocity detection identifies rapid successive transactions, such as card-testing rings or benefit trafficking, by analyzing transaction counts within specific time windows like one minute or one hour. The author provides SQL code examples using window functions to flag anomalies while accounting for legitimate high-frequency users through whitelisting. The second pattern, Impossible Travel, detects physically impossible movement between transaction locations, such as a card being used in Chicago and Los Angeles within minutes, indicating cloning. The article serves as a practical guide for data professionals, offering executable code snippets compatible with major databases like Snowflake and BigQuery, aiming to help teams build robust, initial fraud detection layers on new datasets.