SQL patterns I use to catch transaction fraud

jstanley

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

crmd

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

achierius

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.

sincerely

This is quite interesting, but the blatantly AI generated explanations are like an anti-signal for quality

maciekkmrk

What if I go on a roadtrip and suddenly get gas at 2am?

0cf8612b2e1e

If a card swipes in Chicago and seven minutes later swipes in Los Angeles, one of those swipes is fake. How does this work with online shopping? When I am sitting on the couch and buy from Amazon, where does the address get registered? Can also imagine an edge case: couple shares an online account, one is traveling and purchases with the saved card details.

themafia

> If a card swipes in Chicago and seven minutes later swipes in Los Angeles, one of those swipes is fake. The card is cloned. Or, the cardholder is trying to do the cannonball run: https://www.youtube.com/shorts/Dx5WPNIEwiE

dogscatstrees

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.