> Real cardholders almost never buy something for exactly $1.00. Coffee is $4.73, gas is $52.81. The roundness is the signal.
Surely this depends on how the vendor sets their prices? If you're going to buy something from a website to test a stolen credit card you don't just get to make up your own prices.
And I think you may be over-indexing on the US "prices don't include tax" thing. Elsewhere, round-number prices are extremely common.
In fact a lot of the rest of the stuff in the post seems like it wouldn't work very well either. (E.g. you're flagging anyone who has done a transaction in the last 90 days outside the range of hours at which they have 2+ transactions? Wouldn't that be like 50% of people?).
It's unclear to me whether this article is an attempt at breaking down complex expertise into over-simplified SQL queries, or whether it is all speculative and made up.
There is a conflict between "Six SQL patterns I use to catch transaction fraud" and "Nothing here comes from anything I’ve actually worked on or seen".
Their approach to “Suspicious merchants” also confuses me: the description doesn’t make logical sense to me and doesn’t match the abuse pattern as far as I understand it.
> When a skimmer compromises a card reader at, say, a gas pump, you don’t get one fraud case. You get dozens. Every card swiped at that pump for the next few weeks is now in someone’s database. So the symptom from the merchant side is: an unusual number of unrelated cards spending more than usual, in a short window.
So he checks for hour-bucketed increases in high-value transactions originating from that merchant.
Seems to me like a good way to catch a sale, an opening, a launch event, or a product “drop,” a single high-value sale that somebody spreads across several cards… less so a good way to detect a steady trickle of stolen card data that’s inexplicably used back at the same merchant.
If you’re installing a card skimmer, why would you charge the stolen cards at the same business where you’re stealing them? And why would you concentrate your spending into bursts if the skimmer’s harvesting all day every day?
If you’re the merchant doing the skimming in order to spend at your own store, wouldn’t it be easier to punch a higher amount into the terminal? If you’re a skimming ring, wouldn’t you prefer to have purchasing power rather than this $5000 threshold (?!) of extra gas (plus a giant neon sign advertising where you placed your skimmer)?
Wouldn’t a more sensible approach involve something like looking for merchant clusters in the combined transaction histories of known-stolen accounts?
The LLM runs so strong in this whole enterprise… I want to give the person the benefit of the doubt, but I can’t resist the sneaking suspicion that LLM fabulism to push a slop novel just wasted 15 minutes of my life.
This seems interesting, but has so many signs of AI writing that I worry it's not just edited but generated from whole cloth. Probably still a lot of truth in there but it does give me pause!
The main problem with these SQL calculations is that they are deterministic shortcuts for a probabilistic problem. Fraud is not usually a “true because rule X matched.” It is more like "what is the probability this is fraudulent"? SQL patterns are useful, but they are blunt instruments. I really don't think banks use deterministic heuristics but more data science stuff.
This takes me back, fighting telephone fraud back when folks use to accept cc over the phone. We used similar patterns but only had phone numbers and the white pages. Cross state boundaries inside similar time frames and categorizing similar merchant types. It’s fun to see these same patterns still in use 20 years later for the same purpose.
Isn't the point of ML that you learn these rules from the data? The right approach to me would be to use ML models to detect patterns that correspond with fraud and then evaluate them to see if any make sense. This way you might discover new hyptotheses.
This is the sort of thing I used to love doing and I often gaze at raw data analysis and sometimes wish my career had pivoted towards working with data like this.
But I must admit there was a point where I suddenly lost my love for SQL and it was pretty much when the OVER PARTITION BY syntax appeared.
It never clicks. I always have to look up how it works, I always find it unintuitive. I've never understood why I hate it so much.
> Fraud detection in transaction data is mostly SQL. Not machine learning, not graph databases, not whatever Gartner is hyping this year. SQL, run against the right tables, with the right joins, looking for the right shapes.
It's also not all program-integrity, which is the only work that could justify such blanket statements. Worse is better as long as it addresses the problem domain.
Fintech clients are generally interested in knowing whether a transaction happening _right now_ is fraud. They want to know that in a few milliseconds, for high-dimensional data. It's work done at a scale where relational databases cannot meet these real-time constraints, and instead find other uses like historical data loading. That's how you end up with in-memory databases, stream-processing engines, and yes, even machine learning.
Having said that, some of the author's points are valid, and I'm looking forward for their next writings, in particular dealing with noisy alerts is a general problem beyond performance engineering.
> Border crossings inside 10 minutes. International rings.
Or normal people living in Europe in border-adjacent areas.
Also, I guess you don't include card-not-present transactions in this, but you incorrectly assume that every merchant has their location set correctly. And that every sale happens in a brick-and-mortar establishment, not from travelling salespeople or whatever. And that all transactions happen online.
> Most people are creatures of habit when they spend money. A nine-to-fiver doesn’t suddenly start buying gas at 3am.
Breaking out of a habit once in a while is what keeps one's mind sharp.
A big "fuck you" to financial analysts with those groundhog-day mindsets for making my life much more miserable than it needs to be and for adding a chilling effect to those little getaways that make life interesting and worthwhile. I despise you for this.
We develop tirreno (1), an open-source security framework.
I question the described approaches. For example, while impossible travel is a legitimate and widely used technique, it's related to online user behaviour based on IP address. Moreover, tirreno, for example, has separate rules for cases where the IP clearly comes from Apple Relay or VPN/Tor — those are separate flags. I assume some or all examples are LLM-generated, as the context is mixed up and no one actually collects GPS location in bulk for card swipes.
"Fixel Smith" is an AI-generated person, with an article that has very little to do with fraud analysis. 'This' is also a music artist (1), novelist (2), fraud analyst (3), influencer (4), and whatever else you can imagine.
220+ points and 70 comments, and very few notice it's quite a fake post — and no one that it's an AI generated person?
This is very cool to read. Although I've never truly worked in fraud prevention, I stumbled into automating a lot of similar pattern checks to catch collusion and fraud when I wrote and ran a poker site / casino. Window functions were not available then so the queries were LONG. One way I'd deal with it was to assign uuids to every pair of players who'd ever shared a poker table, and then run nightly analysis of how much their betting deviated from expected norms and their own baseline on each stage of the game if they were in the same hand as each other. This could actually be done in one or two magnificent 100+ line SQL queries on the history table, on a read replica.
Lagging window functions and/or lateral joins probably would have reduced it to 1/4 the size but definitely increased the cost versus just narrowing the sets into smaller tables first.
Reading this to the very end uncovers empty and contradictory advice. I'm almost sure it's LLM generated.
We learn simultaneously that 'your team' shouldn't rely on any one of those patterns ('none of them is enough'), but that pattern 1 'alone will surface a useful amount of fraud'.
We also read strange sentences like "Every analyst on your team will use them (ie window functions) once they exist, and adding the next fraud pattern stops being a project. [end of paragraph]"
Or irrelevant discussions about how filtering by "IS NULL" might be not applicable when almost none of the provided examples uses it (and the one which does uses it in different context).
41 comments
[ 6.8 ms ] story [ 54.6 ms ] threadSurely this depends on how the vendor sets their prices? If you're going to buy something from a website to test a stolen credit card you don't just get to make up your own prices.
And I think you may be over-indexing on the US "prices don't include tax" thing. Elsewhere, round-number prices are extremely common.
In fact a lot of the rest of the stuff in the post seems like it wouldn't work very well either. (E.g. you're flagging anyone who has done a transaction in the last 90 days outside the range of hours at which they have 2+ transactions? Wouldn't that be like 50% of people?).
It's unclear to me whether this article is an attempt at breaking down complex expertise into over-simplified SQL queries, or whether it is all speculative and made up.
There is a conflict between "Six SQL patterns I use to catch transaction fraud" and "Nothing here comes from anything I’ve actually worked on or seen".
> When a skimmer compromises a card reader at, say, a gas pump, you don’t get one fraud case. You get dozens. Every card swiped at that pump for the next few weeks is now in someone’s database. So the symptom from the merchant side is: an unusual number of unrelated cards spending more than usual, in a short window.
So he checks for hour-bucketed increases in high-value transactions originating from that merchant.
Seems to me like a good way to catch a sale, an opening, a launch event, or a product “drop,” a single high-value sale that somebody spreads across several cards… less so a good way to detect a steady trickle of stolen card data that’s inexplicably used back at the same merchant.
If you’re installing a card skimmer, why would you charge the stolen cards at the same business where you’re stealing them? And why would you concentrate your spending into bursts if the skimmer’s harvesting all day every day?
If you’re the merchant doing the skimming in order to spend at your own store, wouldn’t it be easier to punch a higher amount into the terminal? If you’re a skimming ring, wouldn’t you prefer to have purchasing power rather than this $5000 threshold (?!) of extra gas (plus a giant neon sign advertising where you placed your skimmer)?
Wouldn’t a more sensible approach involve something like looking for merchant clusters in the combined transaction histories of known-stolen accounts?
The LLM runs so strong in this whole enterprise… I want to give the person the benefit of the doubt, but I can’t resist the sneaking suspicion that LLM fabulism to push a slop novel just wasted 15 minutes of my life.
This is an underrated CX factor: If my card gets denied when i’m a new customer or exhibiting a new pattern, i’m impressed with their software.
However if they deny a transaction where there is any previous history of me authenticating, then I’m frustrated by their naive paranoid algorithm.
> The roundness is the signal.
> Slight pain, same result.
to point at a few.
Can also imagine an edge case: couple shares an online account, one is traveling and purchases with the saved card details.
Or, the cardholder is trying to do the cannonball run:
https://www.youtube.com/shorts/Dx5WPNIEwiE
chargeback-mcp
or would you turn it all into a markdown file and call it a skill?
But I must admit there was a point where I suddenly lost my love for SQL and it was pretty much when the OVER PARTITION BY syntax appeared.
It never clicks. I always have to look up how it works, I always find it unintuitive. I've never understood why I hate it so much.
It's also not all program-integrity, which is the only work that could justify such blanket statements. Worse is better as long as it addresses the problem domain.
Fintech clients are generally interested in knowing whether a transaction happening _right now_ is fraud. They want to know that in a few milliseconds, for high-dimensional data. It's work done at a scale where relational databases cannot meet these real-time constraints, and instead find other uses like historical data loading. That's how you end up with in-memory databases, stream-processing engines, and yes, even machine learning.
Having said that, some of the author's points are valid, and I'm looking forward for their next writings, in particular dealing with noisy alerts is a general problem beyond performance engineering.
Or normal people living in Europe in border-adjacent areas.
Also, I guess you don't include card-not-present transactions in this, but you incorrectly assume that every merchant has their location set correctly. And that every sale happens in a brick-and-mortar establishment, not from travelling salespeople or whatever. And that all transactions happen online.
> Most people are creatures of habit when they spend money. A nine-to-fiver doesn’t suddenly start buying gas at 3am.
Breaking out of a habit once in a while is what keeps one's mind sharp.
A big "fuck you" to financial analysts with those groundhog-day mindsets for making my life much more miserable than it needs to be and for adding a chilling effect to those little getaways that make life interesting and worthwhile. I despise you for this.
I question the described approaches. For example, while impossible travel is a legitimate and widely used technique, it's related to online user behaviour based on IP address. Moreover, tirreno, for example, has separate rules for cases where the IP clearly comes from Apple Relay or VPN/Tor — those are separate flags. I assume some or all examples are LLM-generated, as the context is mixed up and no one actually collects GPS location in bulk for card swipes.
1. https://github.com/tirrenotechnologies/tirreno
"Fixel Smith" is an AI-generated person, with an article that has very little to do with fraud analysis. 'This' is also a music artist (1), novelist (2), fraud analyst (3), influencer (4), and whatever else you can imagine.
220+ points and 70 comments, and very few notice it's quite a fake post — and no one that it's an AI generated person?
1. https://www.amazon.it/Forged-Soundtrack-Explicit-Fixel-Smith...
2. https://fixelsmith.com
3. https://analytics.fixelsmith.com/
4. https://www.instagram.com/fixeltales/
Lagging window functions and/or lateral joins probably would have reduced it to 1/4 the size but definitely increased the cost versus just narrowing the sets into smaller tables first.
How do you deal with vacations and online shopping. You could be in another country or two in a few hours and purchase from across the world
We learn simultaneously that 'your team' shouldn't rely on any one of those patterns ('none of them is enough'), but that pattern 1 'alone will surface a useful amount of fraud'.
We also read strange sentences like "Every analyst on your team will use them (ie window functions) once they exist, and adding the next fraud pattern stops being a project. [end of paragraph]"
Or irrelevant discussions about how filtering by "IS NULL" might be not applicable when almost none of the provided examples uses it (and the one which does uses it in different context).
This is low quality and too long.