Velocity: The False Positive Factory
Smith’s foundational pattern is simple: group transactions into hour buckets, count them, and flag cardholders with more than ten transactions in a window. The query is intuitive.
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;
The antipattern is deploying only this coarse-grained version. Card-testing rings hit servers in seconds, benefit-trafficking rings take hours. If your only velocity query is an hourly rollup, you are blind to the fastest and slowest shapes of fraud. The production fix is running multiple granularities, one-minute, five-minute, and one-hour windows in parallel, but that surfaces the next failure: forgetting the whitelist. Route operators servicing vending machines, payroll cards for seasonal workers, and parents reloading prepaid transit cards all look like fraud under a naive velocity rule. Without a maintained list of known high-frequency actors, your analysts will spend their mornings dismissing the same five merchants. Alert fatigue is not a morale problem, it is a security problem, because eventually everything gets routed to /dev/null.
For sliding-window velocity, Smith uses QUALIFY, which exists in Snowflake, BigQuery, Databricks, and Teradata but fails elsewhere. The Postgres antipattern is assuming portability and pushing the logic live without testing, only to get a syntax error at 2 AM during an incident:
/* Snowflake/BigQuery */
SELECT
cardholder_id,
timestamp,
count(*) OVER (
PARTITION BY cardholder_id
ORDER BY timestamp
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
) AS tx_in_last_5min
FROM transactions
QUALIFY tx_in_last_5min >= 5;
/* Postgres: wrap it, because QUALIFY doesn't live here */
WITH flagged AS (
SELECT
cardholder_id,
timestamp,
count(*) OVER (
PARTITION BY cardholder_id
ORDER BY timestamp
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
) AS tx_in_last_5min
FROM transactions
)
SELECT * FROM flagged WHERE tx_in_last_5min >= 5;
Slight syntax pain, same result, unless you forget the portability gap and deploy untested.
Impossible Travel: Geography for People Who Skipped Physics
If a card swipes in Chicago and then Los Angeles seven minutes later, something is cloned. The threshold Smith uses is 600 mph, faster than a commercial jet’s cruise speed of roughly 575 mph. This is one of the most reliable signals because there is almost no legitimate reason a single card is in two distant places in seven minutes.
The antipattern is tightening that threshold to, say, 100 mph to catch suspiciously fast ground travel. Congratulations: you are now flagging every airline traveler, every parent driving a child home from camp, and anyone whose phone geolocation hops between two cell towers near a highway. The 600 mph boundary exists precisely because it eliminates legitimate human movement. Lowering it introduces geographic false positives at scale.
Another subtle failure mode: using the query without interrogating how location is encoded. haversine computes great-circle distance between two points. If your data uses ZIP-code centroids or coarse geohashes, two terminals at the same airport can map to different “locations”, and the SQL will claim impossible travel for someone who simply walked from customs to baggage claim. The query is syntactically correct, the data model is lying.
WITH ordered_tx AS (
SELECT
cardholder_id,
timestamp,
location,
LAG(timestamp) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_ts,
LAG(location) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_loc
FROM transactions
)
SELECT
cardholder_id,
prev_ts AS first_tx,
timestamp AS second_tx,
prev_loc AS first_location,
location AS second_location,
EXTRACT(EPOCH FROM (timestamp - prev_ts)) / 60 AS minutes_apart,
haversine(prev_loc, location) AS miles_apart,
haversine(prev_loc, location)
/ nullif(EXTRACT(EPOCH FROM (timestamp - prev_ts)), 0)
* 3600 AS mph
FROM ordered_tx
WHERE prev_ts IS NOT NULL
AND prev_loc <> location
AND mph > 600;
Related shapes in the same family are worth monitoring, two distant cities in the same state within five minutes suggests local cloning rings, multiple ZIP codes inside an hour hints at regional skimmer networks, and border crossings inside ten minutes can flag international fraud rings, but only if your baseline speed respects actual physics.
Static Merchant Thresholds: When Costco Looks Suspicious
Merchant-side detection is where static thresholds go to die. The starting query flags merchants with more than twenty unique cards and over $5,000 in an hour bucket. The antipattern is obvious to anyone who has been inside a Costco on a Saturday: high throughput is not inherently suspicious.
The fix is a rolling baseline computed per merchant. Smith uses a 168-hour trailing average of hourly unique-card counts to account for both daily and weekly seasonality. Tuesday 2 PM at a coffee shop is not the same beast as Saturday 9 AM at the same shop, and a one-week window captures both cycles.
WITH merchant_hourly AS (
SELECT
merchant_id,
date_trunc('hour', timestamp) AS hour_bucket,
count(DISTINCT cardholder_id) AS unique_cards
FROM transactions
WHERE timestamp >= current_date - INTERVAL '60 days'
GROUP BY 1, 2
),
with_baseline AS (
SELECT
*,
avg(unique_cards) OVER (
PARTITION BY merchant_id
ORDER BY hour_bucket
ROWS BETWEEN 168 PRECEDING AND 1 PRECEDING
) AS rolling_avg_cards
FROM merchant_hourly
)
SELECT *,
unique_cards / nullif(rolling_avg_cards, 0) AS spike_ratio
FROM with_baseline
WHERE unique_cards > rolling_avg_cards * 3
ORDER BY spike_ratio DESC;
A spike ratio of 3x is where Smith starts, loose enough to avoid drowning the team in alerts, tight enough to catch a compromised gas pump. The antipattern is not only the static threshold, it is using a window that ignores seasonality entirely. A naive 24-hour average will scream false positives every Monday morning because it compares 9 AM Monday against 9 AM Sunday, which for most retailers may as well be a different planet.
Teams operating on aging data infrastructure often inherit static thresholds baked into decade-old stored procedures. These legacy infrastructure constraints that force SQL antipatterns in banking fraud detection make rolling baselines politically difficult, someone’s coupon-book Perl script from 2008 depends on that $5,000 constant, and now it is “proven in production.”
The NULLs That Ate Your Fraud Ring
This is the antipattern that silently kills correctness. Intro SQL books teach you to check IS NULL. Real transaction tables, especially those fed by legacy mainframes and government benefit systems, use sentinel values like 9999-12-31 for “no end date” or 0001-01-01 for “no start date.”
Write a query that filters with IS NULL against one of these tables, and you are not excluding inactive records. You are excluding exactly none of them. The query returns successfully, the pipeline shows green, and the fraud ring you were hunting just got marked “not suspicious” because your predicate missed the magic date.
Before writing any fraud WHERE clause, inspect the schema and ask what convention is actually in use. This sounds like data engineering 101, yet it gets called out explicitly because analysts have watched it fail repeatedly. In an ecosystem where legacy infrastructure constraints that force SQL antipatterns in banking fraud detection dominate, assuming modern NULL semantics is a rookie mistake that costs millions.
Behavioral Baselines for Accounts Born Yesterday
The off-hours pattern is elegant: build a 90-day profile per cardholder, require at least two transactions in a given hour to consider that hour “normal”, then flag anything outside that range. The antipattern is applying this to accounts created last week.
New accounts have no baseline. A user who signs up, immediately drives to an all-night pharmacy, and buys something at 3 AM will be flagged as fraud by a rule that assumes history. They are not fraud, they are a new customer having a Tuesday. For these accounts, you must fall back to global hour patterns or skip the off-hours rule entirely until the account matures.
Another failure mode: omitting the “two or more in that hour” requirement. Without it, one stray late-night gas station purchase three months ago becomes part of the cardholder’s “normal” hours. You will never flag them again, even if their card is currently being swiped on another continent. The SQL is syntactically fine and operationally blind.
WITH cardholder_hour_pattern AS (
SELECT
cardholder_id,
EXTRACT(HOUR FROM timestamp) AS hour_of_day,
count(*) AS tx_count
FROM transactions
WHERE timestamp >= current_date - INTERVAL '90 days'
GROUP BY 1, 2
),
cardholder_normal AS (
SELECT
cardholder_id,
min(hour_of_day) FILTER (WHERE tx_count >= 2) AS earliest_hour,
max(hour_of_day) FILTER (WHERE tx_count >= 2) AS latest_hour
FROM cardholder_hour_pattern
GROUP BY 1
)
SELECT t.cardholder_id, t.timestamp, t.amount, t.merchant_id
FROM transactions t
JOIN cardholder_normal cn USING (cardholder_id)
WHERE EXTRACT(HOUR FROM t.timestamp) NOT BETWEEN cn.earliest_hour AND cn.latest_hour
ORDER BY t.timestamp DESC;
The FILTER (WHERE tx_count >= 2) guard is doing load-bearing work. Remove it at your peril.
Window Functions: The Fastest Way to Burn Your Warehouse Budget
Pattern 6 in Smith’s framework is the composable layer, materializing LAG(), ROW_NUMBER(), running totals, and time-since-last-transaction as columns so analysts can express new fraud hypotheses as simple filter predicates. It collapses iteration time from weeks to hours.
The antipattern is running window functions over massive unfiltered partitions before narrowing the date range:
/* ANTIPATTERN */
WITH everything AS (
SELECT
cardholder_id,
timestamp,
amount,
merchant_id,
LAG(timestamp) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_ts,
ROW_NUMBER() OVER (PARTITION BY cardholder_id, date(timestamp) ORDER BY timestamp) AS tx_of_day
FROM transactions /* no WHERE clause here */
)
SELECT * FROM everything WHERE timestamp >= current_date - INTERVAL '7 days';
The query looks reasonable, but the window has already touched every row in the table. Smith’s warning is direct: “Filter your date range first, then apply the window, not the other way around.” I have seen a junior analyst burn through an entire warehouse credit budget by running LAG() across two years of transactions before adding the WHERE. The pipeline was technically correct. Finance was technically furious.
When your fraud detection starts handling real throughput, the cost problem intersects with infrastructure architecture. A system that correctly identifies fraud but falls over under load is not a win. Understanding scalability challenges and rate limiting patterns in high-throughput fraud detection systems becomes essential once your decisioning layer has to act on thousands of flagged events per minute without becoming a denial-of-service engine.
Once you do compose the primitives correctly, rules collapse elegantly. Hunting card-testing rings, lots of small charges, all at different merchants, inside a minute, becomes three filters:
WITH tx_with_windows AS (
SELECT
cardholder_id,
timestamp,
amount,
merchant_id,
timestamp - LAG(timestamp) OVER w AS time_since_last,
CASE WHEN merchant_id <> LAG(merchant_id) OVER w
THEN 'changed' ELSE 'same' END AS merchant_change,
ROW_NUMBER() OVER (
PARTITION BY cardholder_id, date(timestamp)
ORDER BY timestamp
) AS tx_of_day
FROM transactions
WHERE timestamp >= current_date - INTERVAL '7 days'
WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp)
)
SELECT *
FROM tx_with_windows
WHERE tx_of_day >= 5
AND time_since_last < INTERVAL '60 seconds'
AND merchant_change = 'changed';
That is the payoff: Boolean filters on top of well-constructed windows. But the construction has to be cheap first.
The Auto-Blocking Trap
The final antipattern is political, not technical: treating a single signal as grounds for auto-blocking. Velocity flagged a user? Block the card. Off-hours purchase? Block the card. Amount ended in .99? Block the card.
Every rule above has legitimate false positives. Vending operators trigger velocity. Grandmas on vacation trigger off-hours and geography simultaneously. Card tests of $1.00 are indistinguishable from someone actually buying a $1.00 gumball. A transaction failing on three or four signals is almost always fraud. A transaction failing on one is probably just a weird Tuesday.
Auto-blocking on a single rule is how you drive customer churn, but it is also how you teach adversaries to game your system. If fraudsters learn that staying under $99.99 avoids a block, they will stay under $99.99. You need composite scoring that pattern 6 enables, where rules feed into a score rather than a binary kill switch. Human review is not a luxury, it is the feedback loop that keeps your thresholds honest.
Compose or Die
Fraud detection SQL lives in a tension between analyst velocity and compute sanity. Window functions and composable filters give you the former, unfiltered multi-year partitions and static thresholds give you the latter, in the worst way possible.
If you are building this from scratch, the sequencing still matters. Start with simple velocity, it surfaces real fraud with minimal infrastructure. But before you build the composable layer, audit your data for sentinel values, baseline your merchants dynamically, instrument your queries for cost, and make sure your blocking logic understands the concept of doubt. A fraud pipeline that catches everything but costs $40K per month to run is a pipeline that finance will shut down, which means you catch nothing.
Most of these patterns still run against batch-loaded tables, so your “real-time” fraud detection is often discovering fraud an hour after it happened. The stolen card is already maxed out. Moving toward real-time data pipelines and CDC patterns that replace batch-oriented SQL antipatterns closes that latency gap, but even then, the SQL underneath still has to be correct. Correctness is the difference between stopping a fraud ring and canceling a grandmother’s prescription refill.
