SQL patterns I use to catch transaction fraud • Hacker News Board

Re: SQL patterns I use to catch transaction fraud

#31

Post by achierius » Sat, May 16, 2026, 7:21 AM UTC

zapkyeskrill wrote: post #27

jorisnoo wrote: post #14

Earlier quoted context omitted.

> Three filters. That’s it. And my favourite most hated pattern, the no no no: > Not machine learning, not graph databases, not whatever Gartner is hyping this year.

Oh shyte, I use (and have used) these for a long time. Guess everything is classes as AI nowadays just yield and use it (everyone thinks you do anyway)

This comment certainly does not scan as AI! Look, this isn't perfect, but it's the best we've got, and so long as AI writing is meaningfully worse than human writing, people are going to try to tell the difference.

Re: SQL patterns I use to catch transaction fraud

#32

Post by bob1029 » Sat, May 16, 2026, 7:25 AM UTC

fny wrote: post #26

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.

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 quite obvious yet. SQL tends to have fewer surprises with regard to regressions and unexpected side effects as things change over time.

Re: SQL patterns I use to catch transaction fraud

#33

Post by rswail » Sat, May 16, 2026, 7:28 AM UTC

dnnddidiej wrote: post #19

2 can be genuine use. I let my partner use my card and I use it on my phone as rfid. Maybe ignore phone usages since they are secured pretty well.

The Apple/Google Pay cards have a DPAN (device account number) that is different to the CPAN of the physical card. It keeps the same issuer (first 6 digits) and the same "last 4" digits, but the others are different.

The DPAN is translated into the CPAN by software at the issuing bank, so it's not identifiable by the merchants.

Merchants get the "last 4" digits, but that's not enough to identify specific CPANs.

Re: SQL patterns I use to catch transaction fraud

#34

Post by WhyIsItAlwaysHN » Sat, May 16, 2026, 7:29 AM UTC

masklinn wrote: post #24

WhyIsItAlwaysHN wrote: post #20

Earlier quoted context omitted.

Exactly, hopefully this is not an autoblock in the future.

> A transaction that fails on three or four of them is almost always fraud. A transaction that fails on one might be your grandma being weird with her debit card on vacation.

The article states that the particular item is a clear sign of fraud. If that was true, then it should be treated in a special manner. A more paranoid bank could enforce it without adhering to this guidance of multi-factor detection.

It isn't though, so balancing it with other rules is fine.

Re: SQL patterns I use to catch transaction fraud

#35

Post by rswail » Sat, May 16, 2026, 7:29 AM UTC

teraflop wrote: post #10

0cf8612b2e1e wrote: post #8

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.

Swiping a card (or inserting, or tapping) is a "card present" transaction. Online shopping, where you type in the card number, is a "card not present" transaction. Retailers and banks can tell the difference.

Also, a Apple/Google/Samsung tap has a different DPAN to the physical card, so it's "card present" but a different card.

Re: SQL patterns I use to catch transaction fraud

#36

Post by epgui » Sat, May 16, 2026, 7:33 AM UTC

bob1029 wrote: post #32

fny wrote: post #26

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.

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.

Re: SQL patterns I use to catch transaction fraud

#37

Post by sheept » Sat, May 16, 2026, 7:33 AM UTC

normie3000 wrote: post #6

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…

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.

Re: SQL patterns I use to catch transaction fraud

#38

Post by chii » Sat, May 16, 2026, 7:35 AM UTC

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 when finding false positives.

Re: SQL patterns I use to catch transaction fraud

#39

Post by saagarjha » Sat, May 16, 2026, 7:36 AM UTC

epgui wrote: post #36

bob1029 wrote: 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.

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

Re: SQL patterns I use to catch transaction fraud

#40

Post by lmz » Sat, May 16, 2026, 7:36 AM UTC

dnnddidiej wrote: post #30

masklinn wrote: post #23

Earlier quoted context omitted.

All of them can be genuine use, these are fraud signals not fraud proofs, and the article does cover this: > What works is running them all and scoring each transaction across the signals. A transaction that fails on three or four of them is almost always fraud. A transaction that fails on one might be your grandma being weird with her debit card on vacation.

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