Patrones de SQL usados para detectar fraude en transacciones

• La detección de fraude muchas veces comienza, antes que con machine learning, por definir bien las tablas y joins y encontrar con SQL patrones anómalos de velocidad, ubicación, monto, comercio y horario
• Velocity busca periodos en los que se concentran transacciones del mismo titular de tarjeta en poco tiempo, y requiere ajustar la ventana temporal, los umbrales y una whitelist de falsos positivos
• Impossible travel usa LAG() y cálculo de distancia para detectar como señal fuerte de tarjeta clonada casos físicamente imposibles, como un pago en Chicago y otro 7 minutos después en Los Angeles
• Anomalías de monto buscan rangos como $1.00, $99.99 o $499.99 que sugieren pruebas de tarjeta o evasión de reglas, aunque no encajan bien en transacciones de beneficios
• Si se combinan aumentos anómalos por comercio, transacciones fuera del horario habitual y columnas derivadas con funciones de ventana, se pueden puntuar las transacciones con múltiples señales y reducir el ciclo de iteración de semanas a horas

---

Patrones de SQL para encontrar indicios de fraude en datos transaccionales

• La detección de fraude muchas veces empieza, antes que con machine learning o bases de datos de grafos, con las tablas y joins correctos y SQL para encontrar comportamientos extraños en las transacciones
• Puede aplicarse a datos donde se mueve dinero y quedan logs, como tarjetas de crédito, reclamaciones médicas, comercio electrónico, POS y programas gubernamentales de beneficios
• En un dataset nuevo, normalmente se van sumando patrones en este orden: velocidad, viaje imposible, anomalías de monto, concentración por comercio, horarios anómalos y señales basadas en funciones de ventana

1. Velocity: demasiadas transacciones en poco tiempo

• Cuando alguien intenta agotar rápido una tarjeta o cuenta robada, aparece un patrón de transacciones concentradas en poco tiempo para un mismo titular
• La consulta base agrupa por hora las transacciones de los últimos 30 días y busca tramos donde la cantidad de transacciones por cardholder_id supera el umbral
• Los ajustes clave son el tamaño de la ventana temporal y el umbral de cantidad de transacciones
  • Se pueden ejecutar en paralelo versiones de 1 minuto, 5 minutos y 1 hora para compararlas
  • Las bandas que prueban tarjetas suelen concentrar operaciones en segundos, mientras que el fraude en beneficios puede moverse a lo largo de medio día, así que la escala cambia
• Los usuarios legítimos también pueden superar el umbral
  • Operadores que administran máquinas expendedoras
  • Personas que recargan tarjetas prepagadas en volumen
  • Después de la primera exploración, hace falta una whitelist de objetivos propensos a falsos positivos
• Un enfoque de ventana deslizante calcula la cantidad de transacciones en los últimos 5 minutos con COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
• QUALIFY funciona en Snowflake, BigQuery, Databricks y Teradata
  • En Postgres hay que envolver toda la consulta en un CTE y filtrar desde afuera

2. Impossible travel: desplazamiento físicamente imposible

• Si una tarjeta se usa en Chicago y 7 minutos después en Los Angeles, es muy probable que una de las dos transacciones sea falsa
• Este patrón es una señal fuerte para detectar tarjetas clonadas, porque casi no hay una razón normal para que una misma tarjeta esté en dos lugares lejanos en cuestión de minutos
• La consulta usa LAG() para traer la hora y ubicación de la transacción anterior, y calcula la distancia y el tiempo entre la ubicación actual y la previa
• haversine es una función para calcular la distancia de gran círculo (great-circle distance)
  • La mayoría de los data warehouses la ofrecen
  • Si no, es una función lo bastante simple como para implementarla
• El umbral de ejemplo es 600mph
  • Como la velocidad de crucero de un jet comercial es de unas 575mph, eso implica una velocidad imposible incluso viajando en avión
  • Si se baja a 100mph, también se detectan desplazamientos rápidos por tierra, pero empiezan a aparecer transacciones legítimas de viajeros frecuentes o de padres que trasladan a sus hijos
• Hay señales adicionales dentro de la misma familia
  • Si hay transacciones en dos ciudades lejanas del mismo estado en menos de 5 minutos, podría indicar una red local de clonación
  • Si hay transacciones en varios códigos ZIP dentro de una hora, podría indicar una red de skimmers moviéndose dentro de una misma región
  • Transacciones que cruzan fronteras en 10 minutos pueden ser una señal de una red internacional

3. Amount anomalies: transacciones anómalas por monto específico

• En el fraude hay patrones de monto que aparecen con frecuencia pero son raros en el uso legítimo
• Las condiciones de ejemplo buscan estos rangos
  • $1.00, $5.00, $10.00
  • >= $99.50 y < $100.00
  • >= $499.50 y < $500.00
• Los montos pequeños en dólares exactos suelen ser una señal de prueba de tarjeta
  • El flujo típico es verificar si un número obtenido de un volcado de tarjetas realmente funciona antes de revenderlo
  • Es raro que el titular real compre algo que cueste exactamente $1.00
  • Un café probablemente cueste $4.73 y la gasolina $52.81, no un monto perfectamente redondeado
• Los montos apenas por debajo del umbral tienen otro significado
  • $99.99 puede buscar evitar el punto donde muchos comercios empiezan a pedir identificación desde los $100
  • $499.99 puede buscar evitar un límite diario de ATM de $500
  • Es una señal de que el actor conoce las reglas y se mantiene justo por debajo
• En transacciones de beneficios, los patrones de montos redondeados no ayudan tanto
  • Los beneficios no suelen probarse como si fueran tarjetas
  • Normalmente, detectar beneficiarios duplicados es una señal más importante

4. Suspicious merchants: concentración anómala a nivel de comercio

• Si un lector de tarjetas en una bomba de gasolina se infecta con un skimmer, eso puede producir no un solo caso, sino decenas de fraudes
• Todas las tarjetas que usaron ese lector durante semanas pueden terminar en la base de datos de alguien
• Desde la perspectiva del comercio, eso aparece como un aumento anormal, en un periodo corto, de tarjetas no relacionadas entre sí, junto con montos totales más altos
• Un ejemplo simple de umbrales agrupa por comercio y hora en los últimos 7 días y calcula
  • cantidad de tarjetas únicas
  • cantidad total de transacciones
  • monto total transaccionado
  • y busca franjas horarias donde haya más de 20 tarjetas únicas y un total superior a $5000
• Los umbrales estáticos tienen problemas de ajuste por tamaño
  • Costco puede superar ese umbral en 90 segundos
  • una librería de usados casi nunca lo hará
• Un mejor enfoque es comparar cada comercio contra su propia línea base histórica
  • agrupar por hora las transacciones de los últimos 60 días
  • calcular el promedio histórico de tarjetas únicas de cada comercio usando sus 168 buckets horarios anteriores
  • y encontrar periodos en los que la cantidad actual de tarjetas únicas supera 3 veces ese promedio histórico
• Los 168 buckets horarios representan las franjas por hora de los últimos 7 días
  • porque la estacionalidad diaria y semanal importa
  • el mismo café no tiene la misma línea base un martes a las 2 p. m. que un sábado a las 9 a. m.
• Como punto de partida puede usarse 3 veces lo habitual
  • es lo bastante flexible para no inundar de alertas
  • y lo bastante estricto para capturar horas realmente anómalas

5. Off-hours: transacciones fuera del horario habitual de una persona

• La mayoría de las personas tiene hábitos de gasto
• Si alguien que trabaja de 9 a 5 de pronto empieza a cargar gasolina a las 3 de la mañana, puede significar que otra persona está usando su tarjeta o que está de viaje
• Si está de viaje puede confirmarse con otras señales
• La consulta calcula, por titular y por hora del día, la cantidad de transacciones de los últimos 90 días, y solo considera horario habitual aquellas horas con al menos 2 transacciones
• Luego detecta si la hora de una transacción nueva cae fuera del rango earliest_hour a latest_hour de ese titular
• La condición interna de “al menos 2 transacciones en esa hora” es importante
  • evita que una sola carga de gasolina nocturna ocurrida por casualidad hace 3 meses pase a formar parte del horario habitual
  • alinea el umbral con un hábito real, no con “algo que pasó una vez”
• La desventaja es que requiere datos históricos
  • las cuentas nuevas no tienen línea base
  • para ellas puede usarse el patrón horario del conjunto total de usuarios, o simplemente saltarse este patrón hasta que la cuenta acumule algunos meses

6. Combinar señales con funciones de ventana

• Los patrones con funciones de ventana no son un tipo de fraude aparte, sino un trabajo preparatorio para volver los cinco patrones anteriores señales combinables
• Se pueden crear por transacción columnas derivadas como estas
  • tiempo transcurrido desde la transacción anterior: timestamp - LAG(timestamp)
  • si cambió el comercio: comparar el merchant_id anterior con el merchant_id actual
  • monto acumulado de las últimas 24 horas: SUM(amount) OVER (...)
  • cuál es la enésima transacción del día: ROW_NUMBER()
• Si estas columnas se materializan, las reglas de fraude se reducen a expresiones de filtro simples
• Una banda que prueba tarjetas puede detectarse con condiciones como
  • quinta transacción o más en el día
  • menos de 60 segundos desde la transacción anterior
  • el comercio es distinto del de la transacción previa
• Si una hipótesis nueva de fraude puede expresarse como un filtro SQL y no como un ticket de ingeniería, el ciclo de iteración baja de semanas a horas
• El resultado es que se puede detectar más fraude y hacerlo más rápido

Cómo usar los patrones en conjunto

• Ningún patrón por sí solo es suficiente
• Cada patrón tiene límites claros
  • Velocity produce falsos positivos, como operadores de máquinas expendedoras
  • los desplazamientos geográficamente imposibles no detectan fraudes que ocurren dentro de una sola gran área metropolitana
  • las anomalías de monto no funcionan bien fuera del contexto de prueba de tarjetas
  • las reglas de horarios anómalos requieren historial
• En la práctica, funciona ejecutar todos los patrones y puntuar cada transacción con múltiples señales
• Las transacciones que activan tres o cuatro señales casi siempre son fraude
• Las que activan solo una señal pueden ser un uso extraño pero legítimo de un titular que está viajando
• Si recién se empieza con detección de fraude, conviene comenzar por Velocity
  • revela una cantidad útil de fraude
  • captura relativamente poca actividad legítima
  • y su costo de ejecución es bajo
• Si ya se tienen implementados los puntos 1 al 5, la siguiente inversión debería ir a columnas base con funciones de ventana
  • una vez creadas, todos los analistas del equipo pueden usarlas
  • y agregar el siguiente patrón de fraude deja de ser un proyecto aparte

Puntos de atención

• Manejo de NULL

  • En tablas de transacciones reales, muchas veces no se usa NULL como en los libros introductorios de SQL
  • Muchos sistemas legacy usan valores centinela como 9999-12-31 para “sin fecha de fin” o 0001-01-01 para “sin fecha de inicio”
  • Si filtras con IS NULL, puedes omitir silenciosamente esas filas
  • Hay que revisar primero la convención de cada tabla antes de escribir la cláusula WHERE

• Falsos positivos

  • Toda regla puede marcar a titulares reales que tienen comportamientos extraños pero legítimos
  • Los casos marcados requieren revisión humana
  • Hace falta un ciclo de retroalimentación para ajustar umbrales según lo que sí y lo que no es fraude real
  • Bloquear automáticamente por una sola regla puede hacer que pierdas clientes

• Privacidad

  • Si los datos contienen PII, hay que respetar las políticas de uso de datos aplicables
  • Primero conviene trabajar con datos desidentificados o de muestra, y usar datos de producción solo con aprobación

• Costo

  • Las funciones de ventana sobre particiones grandes no son baratas
  • Primero hay que filtrar por rango de fechas y luego aplicar las funciones de ventana
  • Si ejecutas LAG() primero sobre 2 años de transacciones de todo el dataset y recién después agregas WHERE, puedes consumir una gran parte del presupuesto de créditos del warehouse

[kaydash]

Es un método que antes se tomaba prestado en los sistemas centrales bancarios y en los sistemas de canales.

[GN⁺]

Comentarios de Hacker News

• Me parece que el criterio de que el verdadero titular de la tarjeta casi nunca compra algo de $1.00 depende de cómo el comercio fija el precio
  Al comprar algo en un sitio web para probar una tarjeta de crédito robada, el comprador no puede elegir libremente el precio
  Además, parece estar pensado demasiado desde contextos como Estados Unidos, donde los impuestos no están incluidos en el precio, y en otras regiones los precios redondos son muy comunes
  También dudo que los otros criterios del artículo funcionen bien. Por ejemplo, si marcas a alguien por haber hecho una transacción fuera de su horario habitual dentro de los últimos 90 días, ¿no terminarías atrapando como a la mitad de la gente?
  No queda claro si es un artículo que simplifica en exceso conocimiento especializado complejo en consultas SQL demasiado simples, o si todo son conjeturas e invención. La frase “seis patrones SQL usados para detectar fraude en transacciones” entra en conflicto con “aquí no hay nada en lo que yo realmente haya trabajado o visto”

  • “Transacción fuera del horario habitual” parece un criterio bastante básico
    Normalmente uno no compra gasolina, café y snacks a las 2 de la mañana, pero cuando eso pasa muy de vez en cuando, es muy probable que se trate de una emergencia personal y no quieras estar llamando al banco en medio de eso
    Sé que un ladrón oportunista también puede operar a esa hora, pero claramente también existe el costo de los falsos positivos
  • Es aún peor. En mi experiencia, el café muchas veces cuesta un monto redondo, y hay gente que al cargar gasolina pone a propósito una cantidad exacta
    Además, hay gasolineras que piden montos predefinidos como 10, 20 o 50 euros
  • Una noche en un bar me dio hambre y quise comprar una bolsa de papas fritas, pero el consumo mínimo con tarjeta era de £5, así que pedí que me cobraran £5 exactos
    Entonces bloquearon la tarjeta por sospecha de fraude y fue bastante molesto. No era algo que quisiera resolver borracho a las 2 de la mañana
    Tal vez me protegieron de mí mismo, pero igual fue inconveniente
  • No sé si todavía se use, pero antes en hoteles o rentadoras de autos a veces hacían una transacción de $1.00 para verificar que la tarjeta de crédito fuera válida antes de reservar una habitación o rentar un vehículo
  • Este método se puede evadir fácilmente agregando un poco de ruido a la transacción de prueba, y también se podría mejorar fácilmente con un análisis estadístico decente
    Y este tipo de reconocimiento heurístico de patrones, donde no esperas una precisión cercana al 100%, ¿no es justamente el tipo de área en la que la IA debería destacar?

• El criterio de “cruzar una frontera en 10 minutos significa crimen organizado internacional” también podría aplicar a personas completamente normales que viven en zonas fronterizas de Europa
  Incluso si se excluyen las transacciones sin tarjeta presente, parece asumir erróneamente que todas las ubicaciones de los comercios están configuradas con precisión, que todas las ventas ocurren en tiendas físicas, que no existe la venta móvil y que todas las transacciones se procesan en línea

  • Hace unas semanas, cuando crucé de Estados Unidos a Canadá, probablemente también fueron como 10 minutos

• Si lo lees hasta el final, se nota que el contenido está vacío y que los consejos se contradicen entre sí. Casi seguro parece un texto generado por un LLM
  Dice que “tu equipo” no debería depender de ningún patrón individual, pero también que el patrón 1 por sí solo puede revelar “una cantidad útil de fraude”
  También hay frases raras como “todos los analistas de tu equipo las usarán, es decir, las funciones de ventana, y agregar el siguiente patrón de fraude deja de ser un proyecto”
  Además, casi todos los ejemplos no usan IS NULL, pero aparece una discusión poco relacionada sobre que el filtrado IS NULL podría no aplicarse, y el único ejemplo que sí lo usa está en otro contexto
  En general es un texto de baja calidad y demasiado largo

• Hacker News, esto hay que señalarlo
  “Fixel Smith” es una persona creada por IA, y el artículo casi no tiene relación con análisis de fraude. Ese nombre se usa para casi cualquier identidad imaginable: músico (1), novelista (2), analista de fraude (3), influencer (4), etc.
  Tiene más de 220 puntos y más de 70 comentarios, pero casi nadie notó que este artículo es bastante falso y nadie vio que se trata de una persona generada 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 últimamente Hacker News ha agarrado la frustrante costumbre de subir este tipo de publicaciones de IA de baja calidad
    Me hace preguntarme si esta inundación de IA revela una verdad incómoda sobre el criterio de la comunidad, o si solo muestra una falla de los mecanismos de defensa existentes que bastaría con cambiar
  • Revisé el artículo desde el celular y solo vi un poco los comentarios. No siempre es fácil decidir si un texto fue generado por IA o editado, pero aquí bastaba ver las citas para que fuera obvio desde el primer momento
    Si asumimos que todos los comentarios fueron escritos de buena fe, entonces es bastante preocupante que incluso aquí haya tan poca alfabetización en IA
  • Incluso a simple vista parece una persona extremadamente prolífica o un bot
    La novela no tiene casi nada que ver con los textos analíticos, y los textos analíticos parecen tener estilo de LLM, así que todo resulta sospechoso. Dado que el tema original es fraude, es irónico
  • Me sorprendería más si la mayoría de la gente investigara por costumbre al autor de lo que lee
    Sinceramente, yo normalmente ni veo la firma del artículo, y mucho menos otras partes del sitio
  • Esto claramente es un artículo que existe de verdad. Por supuesto, parece escrito por un LLM, pero si la peor crítica que se le puede hacer es que parece de LLM, entonces quizá en realidad no haya una crítica sustancial
    No está claro si el contenido es inventado o no, pero se puede criticar el contenido del artículo sin especular si lo escribió un LLM o si es ficción. Tiene defectos mucho más concretos

• Estamos desarrollando el framework de seguridad open source tirreno
  Tengo dudas sobre el enfoque que se describe aquí. Por ejemplo, el viaje imposible es una técnica legítima y muy usada, pero está relacionada con el comportamiento de usuarios en línea basado en direcciones IP
  En tirreno hay reglas separadas para casos en que está claro que la IP viene de Apple Relay o de VPN/Tor, y esas son banderas aparte
  Creo que algunos o todos los ejemplos fueron generados por un LLM. El contexto está mezclado y, además, en pagos con tarjeta no existe en la práctica ningún lugar que recolecte ubicaciones GPS a gran escala

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

• Esto se parece más a lógica basada en reglas codificada en consultas SQL “sin datos que la respalden”
  Hay montones de umbrales, pero no hay datos que demuestren que esos umbrales tengan sentido

• Afirmaciones tajantes como “la detección de fraude en datos transaccionales es mayormente SQL, no machine learning ni bases de datos de grafos ni lo que sea que Gartner esté promoviendo este año” solo se justifican si estás hablando del trabajo completo de integridad programática
  Si resuelven el problema del dominio, un enfoque más simple y más tosco puede ser mejor
  Los clientes fintech normalmente quieren saber si la transacción que está ocurriendo ahora es fraudulenta, y quieren una respuesta en milisegundos sobre datos de alta dimensionalidad. Este es un tipo de trabajo a una escala donde una base de datos relacional tiene dificultades para cumplir esas restricciones en tiempo real, y en cambio se usa para otras cosas, como cargar datos históricos
  Por eso aparecen bases de datos en memoria, motores de procesamiento de streams e incluso machine learning
  Aun así, algunos puntos del autor son válidos, y en particular el problema de lidiar con alertas ruidosas va más allá de la ingeniería de rendimiento, así que espero el siguiente artículo

  • En mi experiencia, lo que se describe se llama más precisamente prevención de fraude que detección de fraude. En configuraciones maduras, ambas coexisten y se complementan
    En prevención siempre estás limitado por los requisitos de latencia, los datos disponibles y una imagen incompleta del comportamiento del usuario. Tomas decisiones rápidas con machine learning y reglas para cubrir la mayoría de los casos, pero por esas limitaciones no puedes bloquear con precisión todo el fraude
    La detección trata las consecuencias posteriores. Es común que un equipo de analistas revise transacciones aprobadas para buscar señales de fraude. Esto es especialmente importante en tipos de fraude donde no hay señales externas como contracargos o quejas de clientes. La integridad de plataformas es un ejemplo, y los sistemas antilavado en fintech también tienen que salir a buscar fraude
    Se complementan porque las transacciones detectadas se convierten en etiquetas para entrenar y evaluar el siguiente modelo de prevención

• Dice que si la tarjeta se usa en Chicago y 7 minutos después se vuelve a usar en Los Ángeles, una de las dos debe ser falsa, pero me pregunto cómo funciona eso con las compras en línea
  Si estoy sentado en el sofá y compro algo en Amazon, ¿qué dirección se registra?
  También parece posible un caso límite donde una pareja comparte una cuenta en línea y una de las personas compra mientras viaja usando la tarjeta guardada

  • Deslizar, insertar o acercar la tarjeta es una transacción con tarjeta presente. Ingresar el número de la tarjeta, como en compras en línea, es una transacción sin tarjeta presente
    Los comercios y los bancos pueden distinguir esa diferencia
  • Se puede distinguir por los metadatos de la transacción. Trabajé en una empresa de tarjetas de crédito
  • Entiendo que el sistema distingue entre tarjeta presente y tarjeta no presente

• “Este enfoque no funciona hasta que se acumula historial, y las cuentas nuevas no tienen línea base” es un factor de experiencia del cliente poco valorado
  Si eres un cliente nuevo o muestras un patrón nuevo y te rechazan la tarjeta, sientes que el software está haciendo bien su trabajo
  Pero si tengo un historial previo autenticado y aun así me rechazan una transacción, me molesta por culpa de un algoritmo ingenuo y paranoico

  • El incentivo del banco es reducir el fraude
    Las transacciones fraudulentas al final terminan en anulaciones o reembolsos, y el banco absorbe esa pérdida. Una transacción rechazada solo crea un cliente enojado, y el cliente se queja y luego se le pasa. Así que el peso de ese costo externalizado recae en el cliente
    Por lo tanto, los bancos tienen incentivos para equivocarse del lado de ser más cautelosos y rechazar transacciones aunque haya falsos positivos

• Me parece que la esencia del machine learning es precisamente aprender este tipo de reglas a partir de los datos
  Creo que el enfoque correcto sería usar un modelo de machine learning para encontrar patrones asociados al fraude y luego evaluar si alguno tiene sentido. Así incluso podrías descubrir nuevas hipótesis

  • Cualquier cosa que no sea explicable y que no pueda mejorarse iterativamente de forma determinista es demasiado riesgosa para el trabajo de rechazar transacciones financieras
    Un analista humano tiene que poder explicarle al equipo de cumplimiento en un correo de 5 minutos por qué se rechazó una transacción concreta y qué se habría tenido que hacer distinto para evitar esa decisión adversa
    Muchas veces, cuando arreglas un problema con machine learning, aparecen dos problemas nuevos que todavía no son evidentes. A medida que cambia con el tiempo, SQL en general da menos sorpresas en términos de regresiones o efectos secundarios inesperados

[aliveornot]

Entonces eso era a lo que se referían con montos redondos... rounded, claro.

[xguru]

¿Por qué lo tradujeron así? :'( Ya lo corregí.