Padrões SQL usados para detectar fraudes em transações

• A detecção de fraude muitas vezes começa, antes de machine learning, por acertar tabelas e joins e encontrar padrões anômalos de velocidade, localização, valor, estabelecimento e horário com SQL
• Velocity encontra intervalos em que transações do mesmo titular de cartão se concentram em pouco tempo, exigindo ajuste de janela de tempo, limiares e whitelist de falsos positivos
• Impossible travel usa LAG() e cálculo de distância para identificar como forte sinal de cartão clonado casos fisicamente impossíveis, como um pagamento em Chicago 7 minutos antes de outro em Los Angeles
• Anomalias de valor procuram faixas como $1.00, $99.99, $499.99, que podem indicar teste de cartão ou tentativa de contornar regras, mas não se encaixam bem em transações de benefícios
• Pico em estabelecimentos, transações fora do horário usual e colunas derivadas com funções de janela permitem pontuar transações com vários sinais e reduzir o ciclo de iteração de semanas para horas

---

Padrões SQL para encontrar sinais de fraude em dados de transações

• A detecção de fraude muitas vezes começa, antes de machine learning ou bancos de dados em grafo, por tabelas e joins corretos e por SQL para encontrar formatos estranhos de transação
• Pode ser aplicado a dados em que dinheiro se move e ficam logs, como cartões de crédito, cobranças médicas, e-commerce, POS e programas governamentais de benefícios
• Em um novo dataset, normalmente os padrões são construídos nesta ordem: velocidade, deslocamento impossível, anomalias de valor, concentração por estabelecimento, horários anormais e sinais baseados em funções de janela

1. Velocity: transações excessivas em pouco tempo

• Quando alguém tenta esgotar rapidamente um cartão ou conta roubada, surge um padrão de concentração de transações em pouco tempo para o mesmo titular
• A consulta básica agrupa as transações dos últimos 30 dias por hora e encontra intervalos em que o número de transações por cardholder_id passa do limite
• Os principais parâmetros de ajuste são o tamanho da janela de tempo e o limiar de quantidade de transações
  • É possível rodar em paralelo versões de 1 minuto, 5 minutos e 1 hora para comparar
  • Grupos de teste de cartão concentram transações em segundos, enquanto grupos de fraude em benefícios podem agir ao longo de meio dia, então a escala muda
• Usuários legítimos também podem ultrapassar o limite
  • operador que gerencia máquinas de venda automática
  • pessoa que recarrega cartões pré-pagos em massa
  • após a primeira investigação, é preciso uma whitelist de alvos de falso positivo
• O método de janela deslizante calcula o número de transações nos últimos 5 minutos com COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
• QUALIFY funciona em Snowflake, BigQuery, Databricks e Teradata
  • No Postgres, é preciso envolver a consulta inteira em uma CTE e filtrar por fora

2. Impossible travel: deslocamento fisicamente impossível

• Se um cartão é usado em Chicago e 7 minutos depois em Los Angeles, há grande chance de uma das duas transações ser falsa
• Esse padrão é um forte sinal para detectar cartões clonados, porque quase não existe motivo legítimo para o mesmo cartão estar em dois lugares distantes em poucos minutos
• A consulta usa LAG() para buscar o horário e a localização da transação anterior, e calcula a distância e o tempo entre o ponto atual e o anterior
• haversine é uma função que calcula a distância de grande círculo (great-circle distance)
  • A maioria dos data warehouses oferece isso
  • Se não houver, é o tipo de função que dá para escrever manualmente
• Um limiar de exemplo é 600mph
  • Como a velocidade de cruzeiro de um jato comercial é cerca de 575mph, isso significa uma velocidade impossível até de avião
  • Se baixar para 100mph, também passa a capturar deslocamentos terrestres rápidos, mas começa a incluir transações legítimas de viajantes reais ou de pais levando filhos
• Há sinais adicionais da mesma família
  • transações em duas cidades distantes do mesmo estado em menos de 5 minutos podem sugerir um grupo local de clonagem
  • transações em vários ZIP codes em menos de uma hora podem sugerir um grupo de skimmers operando numa região
  • transações que cruzam fronteiras em menos de 10 minutos podem ser sinal de grupo internacional

3. Amount anomalies: transações anômalas em certas faixas de valor

• Existem padrões de valor que aparecem com frequência em fraude, mas são raros no uso normal
• As condições de exemplo procuram as seguintes faixas
  • $1.00, $5.00, $10.00
  • de $99.50 até abaixo de $100.00
  • de $499.50 até abaixo de $500.00
• Valores pequenos em dólares inteiros costumam ser sinal de teste de cartão
  • a ideia é verificar se um número obtido em um dump de cartões realmente funciona antes de revendê-lo
  • é raro o verdadeiro titular comprar algo exatamente por $1.00
  • um café tende a custar $4.73, combustível algo como $52.81, e não um valor perfeitamente redondo
• Valores logo abaixo de um limite têm outro significado
  • $99.99 pode ser uma forma de evitar a exigência de documento a partir de $100 em muitos lugares
  • $499.99 pode ser uma forma de evitar um limite diário de ATM de $500
  • isso sinaliza que o fraudador conhece a regra e fica logo abaixo dela
• Em transações de benefícios, padrões de valores redondos não ajudam muito
  • benefícios não são testados com cartão da mesma forma
  • normalmente o sinal mais importante é de beneficiários duplicados

4. Suspicious merchants: concentração anômala por estabelecimento

• Quando um leitor de cartão, como o de uma bomba de combustível, é infectado por um skimmer, isso pode gerar não um caso, mas dezenas de fraudes
• Todos os cartões que usaram aquele leitor durante semanas podem acabar no banco de dados de alguém
• Do ponto de vista do estabelecimento, isso aparece como um aumento acima do normal, em pouco tempo, no número de cartões não relacionados entre si, junto com aumento nos valores transacionados
• Um exemplo simples de critério agrupa por estabelecimento e por hora nos últimos 7 dias e calcula
  • número de cartões únicos
  • total de transações
  • valor total transacionado
  • depois procura horários em que o número de cartões únicos passa de 20 e o total ultrapassa $5000
• Limiares estáticos têm problema de ajuste por porte
  • Costco pode passar desse limite em 90 segundos
  • uma livraria de usados talvez quase nunca passe
• Uma abordagem melhor é comparar cada estabelecimento com sua própria linha de base histórica
  • agrupar os últimos 60 dias por hora
  • calcular a média histórica de cartões únicos para cada estabelecimento com base nos 168 buckets horários anteriores
  • procurar intervalos em que o número atual de cartões únicos passa de 3 vezes a média histórica
• Os 168 buckets horários representam as últimas 7 dias em intervalos de 1 hora
  • porque sazonalidade diária e semanal importa
  • a mesma cafeteria tem uma linha de base diferente numa terça às 14h e num sábado às 9h
• Como ponto de partida, pode-se usar 3 vezes o normal
  • solto o bastante para não gerar alertas demais
  • rígido o bastante para capturar horários realmente anômalos

5. Off-hours: transações fora do horário usual da pessoa

• A maioria das pessoas tem hábitos de gasto
• Se alguém que trabalha das 9h às 17h de repente começa a abastecer às 3h da manhã, pode ser que o cartão esteja sendo usado por outra pessoa ou que o titular esteja viajando
• Se está viajando ou não, isso pode ser confirmado com outros sinais
• A consulta calcula o número de transações por titular e por faixa horária nos últimos 90 dias, e só considera como horário usual as faixas em que houve pelo menos 2 transações
• Depois, detecta quando o horário de uma nova transação fica fora do intervalo entre earliest_hour e latest_hour daquele titular
• A condição interna de “pelo menos 2 transações naquela faixa horária” é importante
  • evita que um único abastecimento noturno casual de 3 meses atrás entre como horário usual
  • ajusta o critério para hábitos reais, e não para “algo que aconteceu uma vez”
• A desvantagem é que dados históricos são necessários
  • contas novas não têm linha de base
  • para contas novas, pode-se usar o padrão geral de horários dos usuários ou simplesmente pular esse padrão até que a conta acumule alguns meses

6. Combinando sinais com funções de janela

• O padrão com funções de janela não é exatamente um tipo separado de fraude, mas uma etapa de preparação para transformar os cinco padrões anteriores em sinais combináveis
• É possível criar as seguintes colunas derivadas por transação
  • tempo decorrido desde a transação anterior: timestamp - LAG(timestamp)
  • se houve troca de estabelecimento: comparação entre o merchant_id anterior e o atual
  • valor acumulado nas últimas 24 horas: SUM(amount) OVER (...)
  • qual é a enésima transação do dia: ROW_NUMBER()
• Ao materializar essas colunas, as regras de fraude se reduzem a expressões simples de filtro
• Um grupo de teste de cartão pode ser encontrado com as condições abaixo
  • quinta transação do dia ou posterior
  • menos de 60 segundos desde a transação anterior
  • estabelecimento diferente do da transação anterior
• Se uma nova hipótese de fraude puder ser expressa como filtro SQL em vez de virar um ticket de engenharia, o ciclo de iteração cai de semanas para horas
• No fim, isso permite pegar mais fraude e mais rápido

Como usar os padrões em conjunto

• Nenhum padrão isolado é suficiente
• Cada padrão tem limitações claras
  • Velocity gera falsos positivos como operadores de máquinas automáticas
  • deslocamento geograficamente impossível deixa passar fraudes que acontecem dentro de uma mesma grande área metropolitana
  • anomalias de valor não funcionam bem fora do contexto de teste de cartão
  • regras de horário anormal exigem histórico
• Na prática, funciona rodar todos os padrões e pontuar cada transação com vários sinais
• Transações que disparam três ou quatro sinais quase sempre são fraude
• Transações que disparam apenas um sinal podem ser só um uso incomum, mas legítimo, de um titular em viagem
• Se você está começando a trabalhar com detecção de fraude, vale começar por Velocity
  • revela um volume útil de fraude
  • captura relativamente pouca atividade legítima
  • também tem baixo custo de execução
• Se você já tem os itens 1 a 5, o próximo investimento é em colunas brutas baseadas em funções de janela
  • uma vez criadas, todos os analistas do time podem usá-las
  • adicionar o próximo padrão de fraude deixa de ser um projeto separado

Pontos de atenção

• Tratamento de NULL

  • tabelas reais de transações muitas vezes não usam NULL como nos livros introdutórios de SQL
  • muitos sistemas legados usam valores sentinela como 9999-12-31 para “sem data de término” e 0001-01-01 para “sem data de início”
  • se você filtrar com IS NULL, pode deixar essas linhas passarem despercebidas
  • é preciso verificar a convenção da tabela específica antes de escrever a cláusula WHERE

• Falsos positivos

  • toda regra pode capturar titulares reais com comportamento estranho, mas legítimo
  • casos sinalizados precisam de revisão humana
  • é necessário um loop de feedback para ajustar limiares com base no que era fraude de verdade e no que não era
  • bloquear automaticamente com base em uma única regra pode fazer você perder clientes

• Privacidade

  • se houver PII nos dados, é preciso seguir as políticas de uso de dados aplicáveis
  • o ideal é trabalhar primeiro com dados desidentificados ou amostras, e só usar dados de produção após aprovação

• Custo

  • funções de janela em partições grandes não são baratas
  • primeiro filtre o intervalo de datas e só depois aplique as funções de janela
  • se você rodar LAG() sobre 2 anos de transações do dataset inteiro e só depois colocar o WHERE, pode consumir muito do orçamento de créditos do warehouse

[GN⁺]

Comentários do Hacker News

• Parece que o critério de que o verdadeiro dono do cartão quase nunca compra algo de $1.00 depende de como o vendedor define o preço
  Quando alguém compra algo em um site para testar um cartão de crédito roubado, essa pessoa não pode escolher livremente o valor da compra
  Além disso, isso parece pensado demais para contextos como o dos EUA, onde o imposto não está incluído no preço, enquanto em outras regiões preços redondos são muito comuns
  Também duvido que os outros critérios funcionem bem. Por exemplo, se você marcar alguém que fez uma transação fora do horário em que normalmente faz duas ou mais transações nos últimos 90 dias, não pegaria algo como metade das pessoas?
  Não fica claro se o texto está apenas simplificando demais um conhecimento especializado complexo em consultas SQL ou se é tudo especulação e invenção. As frases “seis padrões SQL usados para pegar fraude em transações” e “não há nada aqui em que eu realmente tenha trabalhado ou visto” entram em conflito entre si

  • “Transação fora do horário habitual” parece um critério bem básico
    Normalmente eu não compro gasolina, café e lanche às 2 da manhã, mas, quando isso acontece muito raramente, há uma boa chance de ser uma emergência pessoal, e eu não gostaria de ter que ligar para o banco no meio disso
    Entendo que ladrões oportunistas também podem agir nesse horário, mas o custo dos falsos positivos claramente existe
  • É ainda pior. Pela minha experiência, café muitas vezes custa um valor redondo, e há gente que abastece colocando de propósito um valor exato
    Além disso, existem postos que pedem valores pré-definidos como 10, 20 ou 50 euros
  • Uma noite eu estava com fome num bar e fui comprar um pacote de batatas fritas, mas havia um valor mínimo de £5 no cartão, então pedi para cobrarem £5 mesmo
    Aí o cartão foi bloqueado por suspeita de fraude, e isso foi bem irritante. Não era algo que eu queria resolver bêbado às 2 da manhã
    Talvez tenha me protegido de mim mesmo, mas ainda assim foi inconveniente
  • Não sei se ainda usam isso, mas antigamente alguns hotéis e locadoras faziam uma transação de $1.00 para verificar a validade do cartão antes de reservar um quarto ou alugar um carro
  • Esse método é fácil de contornar colocando um pequeno ruído nas transações de teste, e também poderia ser melhorado facilmente com uma análise estatística adequada
    E reconhecimento heurístico de padrões como esse, em que ninguém espera quase 100% de precisão, não seria justamente uma área em que a IA deveria se sair bem?

• O critério de “cruzar fronteiras em 10 minutos é organização internacional” também pode se aplicar a pessoas comuns que vivem em regiões fronteiriças da Europa
  Mesmo excluindo transações sem presença do cartão, parece assumir de forma errada que todas as localizações dos comerciantes estão configuradas com precisão, que todas as vendas acontecem em lojas físicas, que não existem vendedores móveis e que tudo é processado online

  • Algumas semanas atrás, quando fui dos EUA para o Canadá, provavelmente também levei uns 10 minutos

• Se você ler até o fim, aparecem conselhos vazios e contraditórios. Quase certamente parece um texto gerado por LLM
  Diz que “sua equipe” não deve depender de nenhum padrão isolado, mas também afirma que só o padrão 1 já pode revelar “uma quantidade útil de fraude”
  Também há frases estranhas como “todos os analistas da sua equipe vão usá-las, isto é, funções de janela, e adicionar o próximo padrão de fraude deixa de ser um projeto”
  Além disso, quase todos os exemplos nem usam IS NULL, mas aparece uma discussão pouco relevante sobre filtros IS NULL poderem não ser aplicados; e no único exemplo em que aparece, o contexto é outro
  No geral, é um texto longo demais e de baixa qualidade

• Hacker News, isso precisa ser apontado
  “Fixel Smith” é uma persona criada por IA, e o texto quase não tem relação com análise de fraude. Esse nome é usado em praticamente toda identidade imaginável: músico (1), romancista (2), analista de fraude (3), influenciador (4) etc.
  O post tem mais de 220 pontos e mais de 70 comentários, mas quase ninguém percebeu que isso parece bem falso, e ninguém notou que se trata de uma persona gerada por IA

  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/

  • Parece que o Hacker News criou recentemente o hábito frustrante de promover esse tipo de postagem de IA de baixa qualidade
    Fico me perguntando se essa enchente de IA revela uma verdade incômoda sobre o discernimento da comunidade ou se é apenas uma falha dos mecanismos de defesa existentes, algo que bastaria corrigir
  • Eu estava vendo o post no celular e só dei uma passada rápida pelos comentários. Nem sempre é fácil dizer se algo foi gerado por IA ou só editado, mas aqui dava para perceber de cara só pelas citações
    Se assumirmos que todos os comentários foram feitos de boa-fé, então é bem preocupante que até aqui a alfabetização em IA seja baixa
  • Mesmo olhando por cima, parece uma pessoa extremamente prolífica ou um bot
    Os romances quase não têm relação com os textos analíticos, e os textos analíticos parecem ter estilo de LLM, então o conjunto inteiro é suspeito. Considerando que o tema original era fraude, a ironia é grande
  • Eu ficaria mais surpreso se a maioria das pessoas tivesse o hábito de investigar o autor de tudo que lê
    Sinceramente, eu normalmente nem olho a assinatura, muito menos outras partes do site
  • Isso claramente é um texto que existe de fato. Claro que parece escrito por LLM, mas, se a pior crítica que se pode fazer é que parece um LLM, talvez isso não seja uma crítica substancial
    Não está claro se o conteúdo foi inventado ou não, mas dá para criticar o texto sem especular se foi escrito por LLM ou se é ficção. Há falhas muito mais concretas

• Estamos desenvolvendo o framework open source de segurança tirreno
  Tenho dúvidas sobre a abordagem descrita aqui. Por exemplo, viagem impossível é uma técnica legítima e amplamente usada, mas isso está relacionado ao comportamento de usuários online com base em endereços IP
  No tirreno, há regras separadas para casos em que está claro que o IP veio de Apple Relay ou VPN/Tor, e essas são sinalizações distintas
  Acho que alguns ou todos os exemplos foram gerados por LLM. O contexto está misturado, e ninguém coleta em massa localização GPS em pagamentos com cartão na prática

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

• Isso se parece mais com lógica baseada em regras codificada em consultas SQL sem dados que a sustentem
  Há um monte de limiares, mas nenhum dado mostrando que esses limiares fazem sentido

• A afirmação categórica do tipo “detecção de fraude em dados transacionais é majoritariamente SQL, não machine learning, banco de dados em grafo ou qualquer coisa que o Gartner esteja promovendo este ano” só se justifica quando se está falando de todo o trabalho de integridade de programas
  Se resolver o domínio do problema, uma abordagem mais simples e mais grosseira pode ser melhor
  Clientes de fintech normalmente querem saber se a transação que está acontecendo agora é fraude, e querem a resposta em poucos milissegundos sobre dados de alta dimensionalidade. Esse é um tipo de trabalho em escala em que um banco de dados relacional pode ter dificuldade para atender às restrições de tempo real, sendo usado em vez disso para outras finalidades, como carga de dados históricos
  É por isso que entram bancos de dados em memória, engines de processamento de streams e até machine learning
  Ainda assim, alguns pontos do autor são válidos, e especialmente o problema de lidar com alertas ruidosos é uma questão geral que vai além de engenharia de performance, então fico curioso pelo próximo texto

  • Pela minha experiência, o que foi descrito se encaixa melhor em prevenção de fraude do que em detecção de fraude, de forma mais específica. Em configurações maduras, os dois coexistem e se complementam
    Na prevenção, sempre há restrições de latência, dos dados disponíveis e de um retrato incompleto do comportamento do usuário. Machine learning e regras são usados para tomar decisões rápidas e cobrir a maioria dos casos, mas, por causa dessas restrições, não é possível bloquear toda fraude com precisão
    A detecção lida com o que acontece depois. É comum uma equipe de analistas examinar transações aprovadas em busca de sinais de fraude. Isso é especialmente importante em tipos de fraude sem sinais externos como chargeback ou reclamação do cliente. Integridade de plataforma é um exemplo, e sistemas antifraude para prevenção à lavagem de dinheiro em fintech também precisam sair à procura de fraude
    Eles são complementares porque as transações detectadas viram os rótulos para treinar e avaliar o próximo modelo de prevenção

• Disseram que, se um cartão passar em Chicago e 7 minutos depois em Los Angeles, um dos dois é falso, mas fico pensando como isso funciona em compras online
  Se eu estou no sofá comprando algo na Amazon, qual endereço é registrado?
  Também parece possível um caso-limite em que um casal compartilha uma conta online, e uma das pessoas compra durante uma viagem usando os dados do cartão salvos

  • Passar, inserir ou aproximar o cartão é uma transação com presença do cartão. Digitar o número do cartão, como em compras online, é uma transação sem presença do cartão
    Varejistas e bancos conseguem distinguir essa diferença
  • Dá para distinguir pelos metadados da transação. Já trabalhei em uma empresa de cartão de crédito
  • Pelo que eu sei, o sistema distingue cartão presente de cartão não presente

• “Esse método não funciona até haver histórico suficiente, e contas novas não têm linha de base” é um fator subestimado de experiência do cliente
  Se meu cartão é recusado quando sou cliente novo ou quando apresento um padrão novo, parece que o software está fazendo seu trabalho
  Mas, se a transação é recusada apesar de haver um histórico autenticado meu, fico irritado com um algoritmo ingênuo e paranoico

  • O incentivo do banco é reduzir fraude
    No fim, transações fraudulentas viram cancelamento ou reembolso, e o banco absorve a perda. Transações recusadas só criam um cliente irritado, que reclama e logo esquece. Então o peso desse custo externalizado recai sobre o cliente
    Por isso, os bancos têm incentivo a errar pelo lado da cautela e recusar transações mesmo com falsos positivos

• Não é justamente essa a essência de machine learning, aprender essas regras a partir dos dados?
  Acho que a abordagem correta seria usar um modelo de machine learning para encontrar padrões associados à fraude e depois avaliar se algum deles faz sentido. Assim, talvez desse para descobrir novas hipóteses

  • Algo que não seja explicável e deterministicamente refinável é arriscado demais para o trabalho de recusar transações financeiras
    Um analista humano precisa conseguir explicar à equipe de compliance, em um e-mail de 5 minutos, por que uma determinada transação foi recusada e o que deveria ter sido diferente para evitar a decisão adversa
    Muitas vezes, ao corrigir um problema com machine learning, surgem dois novos problemas que ainda não estão claros. Em termos de regressão ou efeitos colaterais inesperados ao longo do tempo, SQL geralmente traz menos surpresas