Six SQL Patterns to Catch Transaction Fraud

Six SQL Patterns I Use to Catch Transaction Fraud

Author: Fixel Smith | Source: analytics.fixelsmith.com | Published: 2026-05-12

---

한 줄 요약

사기 탐지의 대부분은 ML·그래프 DB가 아니라 올바른 조인·shape를 노리는 SQL이며, velocity·impossible travel·amount anomaly·suspicious merchant·off-hours·window function 6개 패턴을 조합·스코어링하는 것이 핵심.

핵심 주장/내용

• 돈이 움직이고 로그된다면 이 쿼리들이 이상을 찾는다(credit card, healthcare claim, e-commerce, benefits)
• 6개 패턴:
  1. Velocity: 시간 윈도우 내 트랜잭션 수 임계 초과(HAVING count(*) > 10). 1분/5분/1시간 병렬 실행 — 다른 사기는 다른 scale. sliding window는 RANGE BETWEEN INTERVAL '5 minutes' PRECEDING
  2. Impossible travel: LAG()로 이전 거래 위치·시각 → haversine 거리/시간 > 600mph(제트기보다 빠름). 카드 클로닝
  3. Amount anomalies: round value($1/$5/$10=카드테스트),threshold직하($99.99 = ID 체크 회피, $499.99 = ATM cap)
  4. Suspicious merchant: 짧은 윈도우에 unique card 급증(skimmer). static 임계보다 merchant를 자기 자신과 비교(168시간 rolling avg의 3배)
  5. Off-hours: cardholder의 정상 시간대(habit, 90일 내 시간당 ≥2회) 밖 거래
  6. Window functions: time_since_last·merchant_change·running_24h_total·tx_of_day를 materialize → 사기 규칙이 filter 표현식으로 collapse(card-testing = tx_of_day≥5 AND time_since_last<60s AND merchant_change='changed')
• 종합: 어느 하나로 충분치 않음 — 모두 실행해 트랜잭션을 신호별로 스코어. 3-4개 실패 = 거의 사기. “분석가가 새 가설을 engineering ticket이 아닌 SQL filter로 표현하면 iteration이 주→시간으로”

주요 수치 / 사실

• QUALIFY(Snowflake/BigQuery/Databricks), Postgres는 CTE wrap
• 빠뜨린 것: NULL 처리(sentinel value 9999-12-31), false positive(human review 필수), PII privacy, cost(window function은 날짜 범위 먼저 필터 — 2년치 LAG()로 warehouse credit 소진 사례)

관련 위키

• Query Optimization
• Data Quality and Validation
• Distributed Systems Reliability

---

Source: 원문 보기