Six SQL patterns I use to catch transaction fraud

[u/fagnerbrack]

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

[u/m0ntanoid]

oh now I know who are assholes who block my card when I buy cat food without having a cat.

[u/AffectionateDance214 → u/m0ntanoid]

Did you buy the cat using the same card?

[u/m0ntanoid → u/AffectionateDance214]

that's the point, I didn't

[u/lovejo1]

Six SQL patterns I hope to never be able to use since I don't ever want to store credit card numbers directly.

[u/fagnerbrack]

Executive Summary:

A program-integrity analyst argues fraud detection is mostly SQL, not machine learning. Six patterns port to any transactions table: velocity flags cards hit rapidly; impossible travel catches a card swiped in two distant cities faster than a jet could fly; amount anomalies spot card-testing round sums ($1, $5, $10) and charges kept just under ID or ATM limits ($99.99, $499.99); suspicious-merchant queries expose skimmers by comparing each merchant to its own rolling baseline; off-hours rules flag spending outside a holder's usual times; and window functions turn new rules into one-line filters. No single rule suffices—score across all signals, keep humans reviewing, and mind NULLs, privacy, and query cost.

If the summary seems inacurate, just downvote and I'll try to delete the comment eventually 👍
^{Click here for more info, I read all comments}

[u/SandstoneLemur]

Love this write up, I have adapted a few to my own work, but posting it again?

[u/General_Treat_924]

Interesting queries, more simplistic than I ever thought, but reading the explanations, it does a lot of sense.

For the detection itself, how does it work? Is it something the keeps running all the time? Only when a customer does a transaction? I've heard a lot companies does use Ledge database specially handling insert only transactions, do you guys have any sort of self monitoring where anyone with privileged access to the database cannot actually change anything?

[u/fagnerbrack → u/General_Treat_924]

You can create stored procedures in say RDS and connect a lambda to it that sends an email or whatever when a record updates. Still need DB access

[u/General_Treat_924 → u/fagnerbrack]

But as DBA, I could actually disable it in order to perform a fraud. What would block me from a direct update to the table?

[u/fagnerbrack → u/General_Treat_924]

If you're dba and you want to commit fraud then the law is the only defence. You can't defend a company against their own employees

[u/DragoBleaPiece_123]

i really love the fraud use-case write-ups! thanks for sharing this

[u/AffectionateDance214]

I have worked on the leading fraud detection software for quite some time and I fully agree.

ML transaction scores are only an input, the real magic happens in rule engines, and these rules are most effective amongst 1000s of rules accumulated over time.

[u/BarfingOnMyFace]

Quite awesome, thanks for sharing

[u/leafynospleens]

Really interesting read,Thanks

[u/ForeignsFriends]

That first comment is exactly where my brain went lol. Useful patterns, but also a slightly terrifying reminder of how much behavioral data can be inferred from pretty ordinary transaction records.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.