거래 사기를 탐지하는 데 사용하는 SQL 패턴

• 사기 탐지는 머신러닝보다 먼저 테이블과 조인을 제대로 잡고, 속도·위치·금액·가맹점·시간대의 이상 패턴을 SQL로 찾는 데서 시작되는 경우가 많음
• Velocity는 짧은 시간 안에 같은 카드 소유자 거래가 몰리는 구간을 찾으며, 시간 창·임계값 조정과 오탐 화이트리스트가 필요함
• Impossible travel은 LAG()와 거리 계산으로 Chicago 결제 7분 뒤 Los Angeles 결제처럼 물리적으로 불가능한 이동을 강한 복제 카드 신호로 잡아냄
• 금액 이상은 $1.00, $99.99, $499.99처럼 카드 테스트나 규칙 회피를 시사하는 금액대를 찾지만, 혜택 거래에는 잘 맞지 않음
• 가맹점 급증, 평소 시간대 밖 거래, 윈도 함수 파생 컬럼을 함께 쓰면 거래를 여러 신호로 점수화하고 반복 주기를 몇 주에서 몇 시간으로 줄일 수 있음

거래 데이터에서 사기 징후를 찾는 SQL 패턴

• 사기 탐지는 머신러닝이나 그래프 데이터베이스보다 먼저 올바른 테이블과 조인, 그리고 이상한 거래 형태를 찾는 SQL에서 시작되는 경우가 많음
• 신용카드, 의료 청구, 전자상거래, POS, 정부 지원 혜택 프로그램처럼 돈이 이동하고 로그가 남는 데이터에 적용 가능함
• 새 데이터셋에서는 보통 속도, 불가능한 이동, 금액 이상, 가맹점 집중, 비정상 시간대, 윈도 함수 기반 신호 순서로 패턴을 쌓아감

1. Velocity: 짧은 시간에 과도한 거래

• 훔친 카드나 계정을 빨리 소진하려는 경우, 같은 카드 소유자에게 짧은 시간 안에 거래가 몰리는 패턴이 나타남
• 기본 쿼리는 최근 30일 거래를 시간 단위로 묶고, cardholder_id별 거래 수가 기준을 넘는 구간을 찾음
• 핵심 조정값은 시간 창 크기와 거래 수 임계값임
  • 1분, 5분, 1시간 버전을 병렬로 돌려 비교 가능함
  • 카드 테스트 조직은 몇 초 안에 거래를 몰아넣고, 혜택 부정 거래 조직은 한나절에 걸쳐 움직일 수 있어 스케일이 다름
• 정상 사용자도 기준을 넘을 수 있음
  • 자판기를 관리하는 운영자
  • 선불카드를 대량 충전하는 사람
  • 첫 탐색 이후에는 이런 오탐 대상 화이트리스트가 필요함
• 슬라이딩 윈도 방식은 COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW로 최근 5분 내 거래 수를 계산함
• QUALIFY는 Snowflake, BigQuery, Databricks, Teradata에서 동작함
  • Postgres에서는 전체 쿼리를 CTE로 감싸고 바깥에서 필터링해야 함

2. Impossible travel: 물리적으로 불가능한 이동

• 한 카드가 Chicago에서 결제되고 7분 뒤 Los Angeles에서 결제되면, 둘 중 하나는 가짜일 가능성이 큼
• 이 패턴은 복제 카드를 잡는 강한 신호이며, 한 카드가 몇 분 안에 먼 두 장소에 있을 정상 사유는 거의 없음
• 쿼리는 LAG()로 직전 거래의 시간과 위치를 가져오고, 현재 위치와 직전 위치 사이의 거리와 시간을 계산함
• haversine은 대권 거리(great-circle distance) 를 계산하는 함수임
  • 대부분의 데이터 웨어하우스가 제공함
  • 없으면 직접 작성할 수 있는 수준의 함수임
• 예시 임계값은 600mph임
  • 상업용 제트기의 순항 속도가 약 575mph이므로, 비행기로도 불가능한 속도라는 뜻임
  • 100mph로 낮추면 빠른 지상 이동도 잡을 수 있지만, 실제 항공 여행객이나 부모가 자녀를 태워오는 정상 거래까지 걸리기 시작함
• 같은 계열에서 추가로 볼 수 있는 신호가 있음
  • 5분 안에 같은 주의 먼 두 도시에서 거래되면 지역 복제 조직을 시사할 수 있음
  • 한 시간 안에 여러 ZIP 코드에서 거래되면 한 지역에서 움직이는 스키머 조직을 시사할 수 있음
  • 10분 안에 국경을 넘는 거래는 국제 조직 신호가 될 수 있음

3. Amount anomalies: 특정 금액대의 이상 거래

• 사기에서는 자주 나타나지만 정상 사용에서는 드문 금액 패턴이 있음
• 예시 조건은 다음 금액대를 찾음
  • $1.00, $5.00, $10.00
  • $99.50 이상 $100.00 미만
  • $499.50 이상 $500.00 미만
• 작은 정수 달러 금액은 대체로 카드 테스트 신호임
  • 카드 번호 덤프에서 얻은 번호가 실제로 동작하는지 확인한 뒤 재판매하려는 흐름임
  • 실제 카드 소유자가 정확히 $1.00짜리 물건을 사는 경우는 드묾
  • 커피는 $4.73, 주유는 $52.81처럼 정확히 둥근 금액이 아닐 가능성이 높음
• 임계값 바로 아래 금액은 다른 의미를 가짐
  • $99.99는 많은 곳에서 $100부터 신분증 확인을 요구하는 선을 피하려는 형태일 수 있음
  • $499.99는 $500 일일 ATM 한도를 피하려는 형태일 수 있음
  • 거래자가 규칙을 알고 그 아래에 머무르는 신호가 됨
• 혜택 거래에서는 둥근 금액 패턴이 크게 도움이 되지 않음
  • 혜택은 같은 방식으로 카드 테스트되지 않음
  • 보통은 중복 수급자가 더 중요한 신호가 됨

4. Suspicious merchants: 가맹점 단위의 이상 집중

• 주유기 카드 리더처럼 특정 리더가 스키머에 감염되면, 한 건이 아니라 수십 건의 사기로 이어질 수 있음
• 해당 리더를 몇 주간 사용한 모든 카드가 누군가의 데이터베이스에 들어갈 수 있음
• 가맹점 관점에서는 짧은 기간에 서로 관련 없는 카드 수가 평소보다 많이 늘고, 거래 금액도 커지는 형태로 나타남
• 단순 기준 예시는 최근 7일 동안 가맹점과 시간 단위로 묶어 다음을 계산함
  • 고유 카드 수
  • 전체 거래 수
  • 총 거래 금액
  • 고유 카드 수가 20개를 넘고 총액이 $5000을 넘는 시간대를 탐색함
• 정적 임계값에는 크기 보정 문제가 있음
  • Costco는 90초 안에 이 기준을 넘을 수 있음
  • 중고 서점은 거의 넘지 않음
• 더 나은 방식은 각 가맹점을 자기 자신의 과거 기준선과 비교하는 것임
  • 최근 60일 거래를 시간 단위로 묶음
  • 각 가맹점의 과거 168개 시간 버킷을 기준으로 평균 고유 카드 수를 계산함
  • 현재 고유 카드 수가 과거 평균의 3배를 넘는 구간을 찾음
• 168개 시간 버킷은 지난 7일의 시간 단위 구간임
  • 일별·주별 계절성이 중요하기 때문임
  • 같은 커피숍도 화요일 오후 2시와 토요일 오전 9시는 기준선이 다름
• 시작점으로는 평소의 3배를 사용할 수 있음
  • 알림이 과도하게 쏟아지지 않을 만큼 느슨함
  • 실제로 이상한 시간대를 잡을 만큼은 빡빡함

5. Off-hours: 개인의 평소 사용 시간대 밖 거래

• 대부분의 사람에게는 지출 습관이 있음
• 9시부터 5시까지 일하는 사람이 갑자기 새벽 3시에 주유를 시작하면, 카드가 다른 사람에게 쓰였거나 여행 중일 가능성이 있음
• 여행 중인지 여부는 다른 신호로 추가 확인 가능함
• 쿼리는 최근 90일 동안 카드 소유자별·시간대별 거래 수를 구한 뒤, 거래가 2번 이상 있었던 시간대만 평소 시간대로 인정함
• 이후 새 거래의 시간이 해당 카드 소유자의 earliest_hour와 latest_hour 범위 밖이면 탐지함
• 내부 쿼리의 “해당 시간대에 2건 이상” 조건이 중요함
  • 3개월 전 우연히 있었던 심야 주유 1건이 평소 시간대에 포함되는 것을 막음
  • 기준을 “한 번 있었던 일”이 아니라 실제 습관에 맞춤
• 단점은 이력 데이터가 필요하다는 점임
  • 신규 계정에는 기준선이 없음
  • 신규 계정은 전체 사용자 시간대 패턴을 쓰거나, 계정이 몇 달 쌓일 때까지 이 패턴을 건너뛸 수 있음

6. 윈도 함수로 신호 조합하기

• 윈도 함수 패턴은 별도 사기 유형이라기보다, 앞선 다섯 패턴을 조합 가능한 신호로 만드는 준비 작업임
• 거래별로 다음 파생 컬럼을 만들어둘 수 있음
  • 직전 거래 이후 경과 시간: timestamp - LAG(timestamp)
  • 가맹점 변경 여부: 직전 merchant_id와 현재 merchant_id 비교
  • 최근 24시간 누적 금액: SUM(amount) OVER (...)
  • 해당 날짜의 몇 번째 거래인지: ROW_NUMBER()
• 이런 컬럼을 물리화하면 사기 규칙이 단순한 필터 표현식으로 줄어듦
• 카드 테스트 조직은 다음 조건으로 찾을 수 있음
  • 하루 5번째 이상 거래
  • 직전 거래 후 60초 미만
  • 가맹점이 직전 거래와 다름
• 새 사기 가설을 엔지니어링 티켓이 아니라 SQL 필터로 표현할 수 있으면, 반복 주기가 몇 주에서 몇 시간으로 줄어듦
• 결과적으로 더 많은 사기를 더 빠르게 잡을 수 있음

패턴을 함께 쓰는 방식

• 어느 한 패턴만으로는 충분하지 않음
• 각 패턴에는 명확한 한계가 있음
  • Velocity는 자판기 운영자 같은 오탐이 있음
  • 지리적으로 불가능한 이동은 한 대도시권 안에서 벌어지는 사기를 놓침
  • 금액 이상은 카드 테스트 맥락 밖에서는 잘 맞지 않음
  • 비정상 시간대 규칙은 이력이 필요함
• 실무에서는 모든 패턴을 돌리고 각 거래를 여러 신호에 걸쳐 점수화하는 방식이 동작함
• 세 개나 네 개 신호에 걸리는 거래는 거의 항상 사기임
• 한 개 신호에만 걸리는 거래는 여행 중인 정상 카드 소유자의 특이한 사용일 수 있음
• 사기 탐지를 처음 시작한다면 Velocity부터 시작하는 것이 좋음
  • 유용한 양의 사기를 드러냄
  • 정상 활동은 비교적 적게 잡음
  • 실행 비용도 낮음
• 이미 1번부터 5번까지 갖췄다면 다음 투자처는 윈도 함수 기반 원시 컬럼임
  • 한 번 만들어두면 팀의 모든 분석가가 사용함
  • 다음 사기 패턴 추가가 별도 프로젝트가 아니게 됨

주의할 점

• NULL 처리

  • 실제 거래 테이블은 SQL 입문서처럼 NULL을 쓰지 않는 경우가 많음
  • 많은 레거시 시스템은 “종료일 없음”에 9999-12-31, “시작일 없음”에 0001-01-01 같은 센티널 값을 사용함
  • IS NULL로 필터링하면 이런 행을 조용히 놓칠 수 있음
  • 특정 테이블의 관례를 확인한 뒤 WHERE 절을 작성해야 함

• 오탐

  • 모든 규칙은 이상하지만 합법적인 행동을 하는 실제 카드 소유자를 잡을 수 있음
  • 플래그가 붙은 건에는 사람의 검토가 필요함
  • 실제 사기와 아닌 것을 기준으로 임계값을 조정하는 피드백 루프가 필요함
  • 단일 규칙으로 자동 차단하면 고객을 잃을 수 있음

• 개인정보

  • 데이터에 PII가 있으면 적용되는 데이터 사용 정책을 지켜야 함
  • 먼저 비식별화 또는 샘플 데이터로 작업하고, 프로덕션 데이터는 승인 후 사용해야 함

• 비용

  • 큰 파티션에서 윈도 함수는 저렴하지 않음
  • 먼저 날짜 범위를 필터링한 뒤 윈도 함수를 적용해야 함
  • 전체 데이터셋의 2년치 거래에 LAG()를 먼저 돌리고 나중에 WHERE를 붙이면 웨어하우스 크레딧 예산을 크게 소모할 수 있음