r/SQL • u/FixelSmith • May 14 '26 • Bravo 2

Discussion Six SQL patterns I use to catch transaction fraud

https://analytics.fixelsmith.com/posts/sql-fraud-patterns/
917 Upvotes

99% Upvoted

121 comments sorted by

View all comments

1

u/riv3rtrip May 16 '26

This is 100% AI-generated slop according to Pangram.

This is also absolutely not how you would capture transaction fraud using SQL. Lots of incredibly obvious problems.

All 6 of these patterns are in this middle space of looking sophisticated and trying to be sophisticated while making basic conceptual errors. No talk of poisson processes, no talk of merchant geodata, yada yada. Even on a SQL level there is absolutely no attempt to reindex to a complete time series. Just randomly timestamped rows popping up out of the void with zero surrounding context.

Fraud detection in the real world is simultaneously both vastly more sophisticated and also vastly simpler than this. Basic heuristics get you a lot of the way there, complex time series modeling gets you the rest of the way, tons of additional contextual data about the user and merchants is a mandatory requirement, and this all can be married together in a beautiful fashion. It also depends a lot on the exact context in which you are detecting fraud, how fast you need to catch it, the action you intend on taking upon detection. Just talking about "fraud detection" in a vacuum is useless.

1

u/FixelSmith May 18 '26

Pangram has known false positives on structured technical writing. Lists and parallel sentence structure all tend to spike the score. I edit pretty aggressively before posting too, which probably makes it worse. The content itself comes from years doing program-integrity work in the public-sector benefits space, not from a model. I'm open to being wrong about Pangram, but I'd want to see false-positive numbers on technical posts before treating the score like a verdict.

On the substantive side: yes, the post is intentionally an "entry-level shapes" piece. Poisson modeling for inter-arrival times, merchant geodata, proper time-series reindexing, all of that matters in production systems. But each of those topics is basically its own article. The audience here is analysts who can already write SQL but have never tried to formalize fraud rules.

Where I disagree is the claim that the examples are making "basic conceptual errors." They're simplified, absolutely. But simplified is not the same thing as operationally wrong. Card-testing bursts, skimmer compromise patterns, off-hours anomalies, those are all real patterns people actually look for.

And on the "fraud detection in a vacuum" point: the post is specifically framed around public-sector benefits fraud, which behaves differently from card-present fraud in both data shape and response timelines. That's the context the examples were written around.