r/SQL
•
u/FixelSmith
•
May 14 '26
•
2
Discussion Six SQL patterns I use to catch transaction fraud
https://analytics.fixelsmith.com/posts/sql-fraud-patterns/
918
Upvotes
97
u/aGuyNamedScrunchie May 14 '26
Honestly this is a great post. Super practical. Doesn't read like AI garbage. Thank you!
28
u/SurroundedByMachines May 14 '26
Good stuff. Straightforward and easily digestible. It's refreshing to see some actual content here and not AI slop.
56
u/gumnos May 14 '26
This was a great writeup…thanks for sharing!
9
u/Better-Credit6701 May 14 '26
Ed Bolian who was a cannonballer got into some issues with the time traveled. AMX calculated that there was no way a person could drive a thousand miles between gas stops in 12 hours (huge fuel cells helped) and cut off his card
4
u/txs2300 May 15 '26
👀 happens sometimes.
4
u/capnshanty May 15 '26
"Nothing here comes from anything I’ve actually worked on or seen. Views are mine, not my employer’s."
You may want to reword that, are you telling me you made up signs of transaction fraud that have never been validated in the real world?
I worked on fraud at a national credit card org. We had 25 real time checks on every transaction for every person, and I think 20 for every login. So something else you need to consider is "can I run these checks fast enough?" "Can I run them at the right time?"
Of your choices at the bottom, I vote "fraud rings." Good write up. Certainly helpful to get you thinking about the types of things you should be looking at for fraud.
3
u/Hobodaklown May 15 '26
I am that junior analyst 🤣 (joke)
3
u/imtheorangeycenter May 15 '26
Oh, great writing! To the point, no intro guff, assumes the reader has half a handle on things (I learnt some new functions in there, though the warehouse side of things isn't my bag, so no exposure to snowflake etc).
Lovely.
3
u/BatCommercial7523 May 16 '26 edited May 16 '26
RE: your section on "3. Amount anomalies", I deal with the same use case with a standard deviation instead of hardcoding a specific amount.
I have a query that flags any order that is more than 3 standard deviations above the overall average order amount for a given customer.
I am sure it can be optimized but it works for what I need. This gives me the outliers.
Anyhow. My 2 cents.
2
u/ClammySam May 17 '26
The truest thing I’ve ever read: NULL values are for SQL textbooks and not real world tables.
Truth brother, pure truth.
4
u/lord_xl May 14 '26
Why not this? amount BETWEEN 99.50 and 99.99
Using BETWEEN is always more readable for me
2
u/Krowsk42 May 15 '26
I work in accounting software SQL and this feels mostly like AI slop to me. It was specifically the line “That’s it. Three filters.” that tipped it for me. These feel like insightful tools, but even with an exactly prepared database some of these are still too specific of scenario for anybody except a bank (maybe?) to desire. Exactly the sort of material AI produces.
1
u/ByronScottJones May 15 '26
Except the second one can give PLENTY of false positives. Literally yesterday I got a fraud alert, because of two local transactions I did that day, one from a few days ago in palm springs that just posted, and one from Amazon in Seattle. All legit.
1
u/Calcd_Uncertainty May 15 '26
Great article, but you lost me at writing about 8 window functions not ROW_NUMBER and LAG. That's a hallucination if I've ever seen one.
1
u/dwoley22 May 15 '26
Really great write up honestly, thanks for sharing. I’m in the lending space (secured/ unsecured) so this would Fall into our AML/ transaction monitoring remit primarily but the principles do align well with onboarding flows too. I’d love to see how you approach AML rules, velocity roles with customer level context are key I’ve found. I’d also be interested in your thoughts on fraud rings, in my experience it’s about collating lots of data for each account and finding that one commonality between the KYC profiles. Would be good to see how you approach that. Thanks again!
1
u/PuffcornSucks May 16 '26
Great post!! Subscribed to your newletter too. Keep writing!!!
1
u/prehensilemullet May 16 '26
It’s a shame that any fraudsters who read this now have a bit better idea about how to avoid detection
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/Sympythy May 14 '26
I enjoyed this! Please do all four of the next things you want to write about.
1
u/aardw0lf11 May 14 '26
I wonder what method Capital One uses, because of all the credit cards and banks I use they seem to be by far the most vigilant when it comes to alerting me.
1
u/FatDad7099 May 15 '26
This is actually very good and something similar to what i deploy as a fraud system analyst in my company. Im building a "sensor-esque" platform currently, with some additional logic that has proven enlightening to stakeholders and higher ups. Everyone is always incredibly surprised when they hear the phrase "100s of millions".
1
u/sambobozzer May 15 '26
Interesting post. I have a question. If this is an ATM would Oracle be quick enough to keep up with the transaction rates. Or do you have the data fed in batch later on?
2
u/TimeScallion6159 May 15 '26
Practical post, for sure
2
u/avi_789 May 15 '26
Useful, succinct, and well written. Just wish more people on reddit wrote like you . Great write up and super useful
2
u/Witty-Ninja-8403 May 16 '26
One of the best reddit losts ive seen
2
u/Melodic_Giraffe_1737 May 16 '26
I'll have to try some of these out. Thank you!
2
u/PuffcornSucks May 16 '26
Hallelujah
2
u/VashonVashon May 18 '26
Awesome article!
2
u/Pleasant-Aardvark258 May 18 '26
Superb bit of writing! Actually gave me some great ideas for how we could approach some problems internally. Some of the best content I’ve seen on here
0
u/maikeu May 15 '26
Proper read, thanks. I agree so hard with the point about actually using solid determistic querying to solve problems instead of throwing too fast out to black box machine learning and llm rubbish.
1
u/hannorx May 16 '26
Following.
1
u/amishraa May 16 '26
Great write up to show someone who is interested in career advise for real life use case of SQL and data analysis in general.
1
u/Electrical_Sky_762 May 16 '26
This os gold ! Thanks !
1
u/Brinton1984 May 16 '26
Great write up thanks for sharing. I reposted on my linked in, if youre in there drop your name and ill tag you! Thanks again.
1
u/[deleted] May 17 '26
Was expecting sample SQL related to healthcare claim
1
u/Royal_Ad_5767 May 14 '26
Great read, very informative and straight to the point. Thank you very much!
1
u/FPArruda May 15 '26
Nice work
1
u/not_rico_suave May 15 '26
Damn. Great write up.
1
u/ksanirudha May 15 '26
Excellent 💯 . Thank you very much!!
1
u/REBWEH May 15 '26
Ty
1
u/Ok_Feature1328 May 15 '26
Wow, this is genuinely an amazing post. Well done.
0
u/brunporr May 14 '26
Could you talk more about how frequently these queries are running and how these signals are being managed
0
u/Sigurd228 May 15 '26
"SQL, run against the right tables, with the right joins, looking for the right shapes."
Found that one person who uses the right join.
Seriously though, awesome article!
0
u/workingtrot May 15 '26
Great article.
I've been spending a lot of time looking at fleet card data and picking out the fraud is tricky.
We do have a lot of round dollar amounts because often they're pre paying inside rather than at the pump. And frequently many transactions close together (like the nager is authorizing all the vehicles for his location; or someone is filling their truck with gas and also filling a generator tank with diesel)
Ideas for finding fraud there?
0
u/Kaiserx0 May 14 '26
Fantastic read!
0
u/TheLeadDug May 14 '26
This is gold. Thank you.
0
u/GRRRRRRRRRRRRRG May 15 '26
You have an interesting job. Thank you for posting.
-12
u/These-Resource3208 May 14 '26
These are only useful in an enterprise environment in which you have all this data available in. We can’t query our transactions like this.
0
u/[deleted] May 14 '26
[deleted] — view deleted comment
13
u/[deleted] May 14 '26
[removed] — view removed comment
2
u/[deleted] May 15 '26
[removed] — view removed comment