Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL patterns I use to catch transaction fraud

analytics.fixelsmith.com

Post reply on HN
Print view View original

41–50 of 138 posts

Re: SQL patterns I use to catch transaction fraud

#41

Post by enoent »

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

Re: SQL patterns I use to catch transaction fraud

#42

Post by Kwpolska »

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

Re: SQL patterns I use to catch transaction fraud

#43

Post by kikimora »

chii wrote: post #38
crmd wrote: post #3

> Drawback: this doesn’t work until you have history. New accounts have no baseline. 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 incentives of the bank is to cut fraud. Fraudulent transactions will eventually cost the bank (when they would have to reverse/reimburse it and eat the loss). A denied transaction only results in an angry customer who will quickly forget after they complained - so the customer bears the brunt of the externalized cost. Therefore, the bank's incentive is to err on the side of more caution, and deny transactions whe…

When someone disputes a charge and wins banks charge processor and the original merchant. The bank won’t loose a penny, merchant will.

Re: SQL patterns I use to catch transaction fraud

#44

Post by nswango »

jstanley wrote: post #2

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

The "transaction outside usual hour range" seems pretty basic.

I don't usually buy gas, coffee or snacks at 2am. But on the very rare occasion that I do, I'm dealing with some kind of personal emergency and don't also want to have to call my bank.

I get that that's also a time opportunistic thieves, etc, might be operating. But the cost of false positives is also a thing.

Re: SQL patterns I use to catch transaction fraud

#45

Post by ande-mnoc »

achierius wrote: post #4

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 roundness is the signal. > Slight pain, same result. to point at a few.

> The “two or more in that hour” filter on the inner query is doing important work.

This is Claude talking isn’t it.

Re: SQL patterns I use to catch transaction fraud

#46

Post by nswango »

saagarjha wrote: post #39
epgui wrote: post #36

Earlier quoted context omitted.

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.

I assume that this option is unlocked once you enter litigation against the company.

Umm no, they submit thousands of random pages of business communication and system spec in discovery. This does not include the source code of their algorithm, which in any case if not stored in any form which can be recreated and shared. If you pay a lawyer a million bucks to read them all it would say that they don't know how the algo works. At the same time they offer you low four digits to make the case go away, if you have a case. If you don't have a valid case at all, they rapidly spend $250,000 on filings and motions which you would have to spend $100,000 to stay in the game.

Re: SQL patterns I use to catch transaction fraud

#47

Post by dnnddidiej »

lmz wrote: post #40
dnnddidiej wrote: post #30

Earlier quoted context omitted.

> If a card swipes in Chicago and seven minutes later swipes in Los Angeles, one of those swipes is fake. The card is cloned. This is the most uncontroversial fraud signal you’ll find — there’s almost no legitimate reason a single card is in two distant places in seven minutes.

The question is whether they would treat that as a single card (physical vs digital).

If they don't then this is a good detection system for a very specific scenario (but nonetheless a good trick)

Re: SQL patterns I use to catch transaction fraud

#50

Post by mike_hock »

jstanley wrote: post #2

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

This is also

a) trivial to bypass by adding dither to the test transactions and

b) trivial to improve upon with proper statistical analysis and

c) shouldn't this kind of heuristic pattern recognition with no expectation of near-100% accuracy be what AI is good at?

Post reply on HN