Live data from Hacker News

SQL patterns I use to catch transaction fraud

analytics.fixelsmith.com

61–70 of 138 posts

Re: SQL patterns I use to catch transaction fraud

#61
Hacker News, we need to talk!

"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/

Re: SQL patterns I use to catch transaction fraud

#62

Hacker News, we need to talk! "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-Expl…

I'd be more surprised to hear that most folks made a habit of investigating the people whose articles we read. To be honest, I usually don't even look at the byline, let alone the rest of the website.

Re: SQL patterns I use to catch transaction fraud

#63
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.

Re: SQL patterns I use to catch transaction fraud

#64
post #62

Hacker News, we need to talk! "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-Expl…

I'd be more surprised to hear that most folks made a habit of investigating the people whose articles we read. To be honest, I usually don't even look at the byline, let alone the rest of the website.

I'm one of the creators of an open-source security framework (1). I've been eating online fraud for breakfast for 8 years. The article is delusional enough that I had to visit the top page of the domain (2).

1. https://github.com/tirrenotechnologies/tirreno

2. https://fixelsmith.com

Re: SQL patterns I use to catch transaction fraud

#65
post #37

Earlier quoted context omitted.

Worse than that. Coffee usually _is_ a round number in my experience, and I know of people who aim for round numbers when filling their car, and of fuel stations which require a pre-set value, often 10, 20, 50€ etc

Yes, as your parent comment points out, the article centers itself on US transactions, where listed prices seldom include tax and are frequently a cent below a round number. For example, the menu says a dish is $15.00 but the restaurant charges $18.83 after tax and tip. Globally, there's no doubt the US is the exception rather than the norm.

That sounds reasonable for some states but 5 states have no sales tax and many states have exclusions to sales tax. Many of those are also likely to have rural areas where small businesses like to use even amounts.

Re: SQL patterns I use to catch transaction fraud

#66
post #41

> 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 i…

In my experience, what you're describing would more specifically be called Fraud Prevention rather than Fraud Detection. Both tend to coexist and are complementary in a mature setup.

For Prevention, you're always going to be constrained by latency requirements, available data and an incomplete picture of user behaviour. You make a quick decision using ML and rules that deals with the majority of cases. But those constraints make it impossible to precisely prevent all fraud.

Detection deals with the downstream consequences of this. A team of analysts will typically analyse the accepted transactions for signs of fraud. This is particularly important for fraud types where you don't get an external signal like a chargeback or customer complaint. Platform integrity is one such example. But Fintechs will also see this building anti-money laundering systems - you need to go looking for the fraud. This is the process the article is describing.

I say they're complementary because the detected transactions become the labels for training and evaluating the next iteration of prevention models.

Re: SQL patterns I use to catch transaction fraud

#67

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.

I have a fair amount of experience in this industry, albeit a couple of years old now. I worked at Square on their payment risk team in 2015 and 2016, at Plaid om their ACH fraud API product called Signal from 2021 to 2024. At Plaid I was involved in client meetings and learned how many companies were already handling risk, and I've interviewed at a handful of other companies' risk teams when I was looking for a new role.

Basically it's not just banks and formal financial institutions doing this, and how they do it depends on the company size. Size tends to correlate not only with how many resources you have for a risk team, but also with whether fraud rings are targeting you.

Usually what I've seen is that companies start with some kind of batch SQL/simple logic process that runs daily and tends to flag accounts for manual review and block automatic events like settlement or trading (or whatever the platform does) until that review has been done. Then over time the company will transition to an ML-based approach that still mostly flags things for manual review. The goal of the ML is to improve the precision of the flagging without hurting dollar recall or fraud event recall too much. Depending on the payment system companies may be sensitive to both (for example, in ACH if you get too many returns, even very low dollar payment returns, you're going to get a hard time from your partner bank and you risk not being able to use ACH anymore).

Re: SQL patterns I use to catch transaction fraud

#69

Hacker News, we need to talk! "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-Expl…

I could imagine a person having or doing all of these over time, people do have many interests, but a cursory glance does give an impression of AI. The Instagram account uses a lot of it at least, and the top domain was likely made in conjunction with AI, given the style.

Kind of fascinating, though it could still be a person doing this using AI as opposed to an entirely generated persona. Thanks for bringing it up.

Re: SQL patterns I use to catch transaction fraud

#70
post #36
post #32

Earlier quoted context omitted.

Anything that can't be explained and iterated deterministically is too risky for the business of declining financial transactions. Human analysts need to be able to explain to compliance in a single 5 minute email why a specific transaction was declined, and most importantly, what could have been done differently to avoid the adverse decision. Fixing one problem with ML often creates two new problems that aren't quit…

In my experience, Visa support can’t tell me why my legitimate transaction was tagged as fraudulent, other than to say it triggered an AI thing. They also can’t tweak the settings like they used to do, but they can manually allow specific transactions one by one on an ad hoc basis.

Recently, they've stopped even being able to allow specific transactions through for me. They can tag the flagged transaction as legitimate and hope the AI picks up on that, but that hasn't worked once in the last ~15 calls for me. I've just stopped trying to use Visa as my primary card online, a habit that bled into in-person purchases as well.
Post reply on HN