On May 12, 2026, a data analyst working on program-integrity for government-funded benefit programs published six SQL query patterns used to detect transaction fraud. The patterns, designed for generic transaction tables, apply to credit cards, healthcare claims, e-commerce, and point-of-sale systems. The analyst emphasized that fraud detection relies on SQL, not machine learning or graph databases, and shared code snippets for velocity checks, impossible travel, and amount anomalies. 1
The first pattern, called 'Velocity,' identifies rapid transactions from a single cardholder. The analyst provided a SQL query that groups transactions by cardholder and hour, flagging any cardholder with more than 10 transactions in a 30-day window. The query uses two adjustable parameters: the time window (e.g., 1-minute, 5-minute, or 1-hour) and the transaction count threshold. The analyst noted that different fraud types appear at different scales—card-testing rings may hit a server in seconds, while benefits-trafficking rings may take an afternoon. 1
For sliding-window velocity checks, the analyst shared a second query using window functions. This query calculates the number of transactions in the last 5 minutes for each cardholder and flags those with 5 or more transactions. The query uses the `QUALIFY` clause, which is supported in Snowflake, BigQuery, Databricks, and Teradata. For PostgreSQL, the analyst noted that the query must be wrapped in a Common Table Expression (CTE) and filtered externally. False positives, such as vending machine operators or bulk prepaid card reloads, are managed with a whitelist. 1
The second pattern, 'Impossible Travel,' detects cloned cards by identifying transactions in geographically distant locations within an unrealistic timeframe. The analyst provided a SQL query that calculates the time and distance between consecutive transactions for each cardholder. Transactions occurring in two locations more than 600 miles apart within 7 minutes are flagged as fraudulent. The query uses the Haversine formula to calculate great-circle distance, a function available in most data warehouses. 1
The analyst adjusted the impossible travel threshold to 600 mph, slightly above the cruise speed of commercial jets (575 mph), to account for air travel. Lowering the threshold to 100 mph could catch ground-based fraud but risks flagging legitimate travelers, such as airline passengers or families driving home from camp. Additional variations of the pattern include detecting transactions in distant cities within the same state in under 5 minutes, multiple ZIP codes within an hour, or border crossings within 10 minutes. 1
The third pattern, 'Amount Anomalies,' targets specific transaction amounts disproportionately common in fraud. The analyst shared a SQL query filtering transactions with amounts between $99.50 and $100.00, $499.50 and $500.00, or exact values of $1.00, $5.00, or $10.00. These amounts often appear in fraudulent activity, such as card testing or benefits trafficking, but rarely in legitimate transactions. The query sorts results by cardholder and timestamp for further investigation. 1
The analyst emphasized that these SQL patterns are adaptable across industries. While their work focuses on government-funded benefit programs, the same queries can be applied to credit card transactions, healthcare claims, or e-commerce platforms. The key, they noted, is identifying the 'right shapes' in transaction data—patterns that deviate from normal behavior and indicate fraudulent activity. The examples provided use generic transaction tables and fictional scenarios to avoid disclosing sensitive data. 1
The analyst included a disclaimer clarifying that the examples are not derived from real cases or employer data. The views expressed are their own and do not reflect those of their employer. The post was published on May 12, 2026, and targets data professionals working on fraud detection, program integrity, or transaction monitoring. The SQL snippets are designed to be run against standard transaction tables with fields like `cardholder_id`, `timestamp`, `location`, and `amount`. 1