Templates SQL anti-fraude

Tous les templates utilisent une CTE fraud_ids pour la liste des fraudeurs. Remplacer $FRAUD_IDS par le array SQL depuis known-fraudsters.md, ou fournir une nouvelle liste.

CTE commune : liste des fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
)

A. Vue d'ensemble (populations)

SELECT
  COUNT(DISTINCT "customerId") AS total_withdrawers,
  COUNT(*) AS total_withdrawals,
  SUM(value) AS total_amount
FROM wallet_transactions
WHERE kind = 'withdrawal' AND status = 'confirmed'
  AND "createdAt" >= '$START_DATE' AND "createdAt" < '$END_DATE'

B. Profil par fraudeur

Metriques completes pour chaque fraudeur : age du compte, topups, retraits, delais, IBANs.

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
topups AS (
  SELECT t."customerId",
    COUNT(*) FILTER (WHERE t.status = 'confirmed') AS confirmed,
    COUNT(*) FILTER (WHERE t.status = 'declined') AS declined,
    COUNT(*) AS total_attempts
  FROM wallet_transactions t
  WHERE t.kind IN ('topup_card','topup_checkout')
    AND t."customerId" IN (SELECT id FROM fraud_ids)
  GROUP BY t."customerId"
),
max_burst AS (
  SELECT t."customerId", MAX(cnt) AS max_day_attempts
  FROM (
    SELECT "customerId", "createdAt"::date, COUNT(*) AS cnt
    FROM wallet_transactions
    WHERE kind IN ('topup_card','topup_checkout')
      AND "customerId" IN (SELECT id FROM fraud_ids)
    GROUP BY "customerId", "createdAt"::date
  ) t
  GROUP BY t."customerId"
),
wd_delay AS (
  SELECT DISTINCT ON (w.id)
    w."customerId",
    EXTRACT(EPOCH FROM (w."createdAt" - t."createdAt")) / 3600 AS delay_h
  FROM wallet_transactions w
  INNER JOIN wallet_transactions t
    ON t."customerId" = w."customerId"
    AND t.kind IN ('topup_card','topup_checkout')
    AND t."createdAt" < w."createdAt"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  ORDER BY w.id, t."createdAt" DESC
),
wd_agg AS (
  SELECT "customerId", MIN(delay_h) AS min_delay_h FROM wd_delay GROUP BY "customerId"
),
iban_delay AS (
  SELECT w."customerId",
    MIN(EXTRACT(EPOCH FROM (w."createdAt" - pm."createdAt")) / 3600) AS min_iban_delay_h,
    COUNT(DISTINCT pm.id) AS nb_ibans
  FROM wallet_transactions w
  JOIN payment_methods pm ON pm.id = w."paymentMethodId"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  GROUP BY w."customerId"
)
SELECT
  c.id, c.email,
  SPLIT_PART(c.email, '@', 2) AS domain,
  c."createdAt"::date AS account_created,
  tp.confirmed AS topup_confirmed,
  tp.declined AS topup_declined,
  mb.max_day_attempts,
  ROUND(wa.min_delay_h::numeric, 1) AS min_topup_wd_hours,
  ROUND(id2.min_iban_delay_h::numeric, 1) AS min_iban_wd_hours,
  id2.nb_ibans
FROM fraud_ids f
JOIN customers c ON c.id = f.id
LEFT JOIN topups tp ON tp."customerId" = f.id
LEFT JOIN max_burst mb ON mb."customerId" = f.id
LEFT JOIN wd_agg wa ON wa."customerId" = f.id
LEFT JOIN iban_delay id2 ON id2."customerId" = f.id
ORDER BY c."createdAt"

C. Regle cooldown post-topup (flat)

Nb fraudeurs et legit catches par seuil de cooldown.

Cote fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
withdrawals AS (
  SELECT DISTINCT ON (w.id)
    w."customerId",
    EXTRACT(EPOCH FROM (w."createdAt" - t."createdAt")) / 3600 AS delay_h
  FROM wallet_transactions w
  INNER JOIN wallet_transactions t
    ON t."customerId" = w."customerId"
    AND t.kind IN ('topup_card','topup_checkout')
    AND t."createdAt" < w."createdAt"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  ORDER BY w.id, t."createdAt" DESC
),
per_customer AS (
  SELECT "customerId", MIN(delay_h) AS min_delay_h FROM withdrawals GROUP BY "customerId"
)
SELECT
  COUNT(*) FILTER (WHERE min_delay_h < 2) AS caught_2h,
  COUNT(*) FILTER (WHERE min_delay_h < 6) AS caught_6h,
  COUNT(*) FILTER (WHERE min_delay_h < 12) AS caught_12h,
  COUNT(*) FILTER (WHERE min_delay_h < 24) AS caught_24h,
  COUNT(*) FILTER (WHERE min_delay_h < 48) AS caught_48h,
  COUNT(*) AS total
FROM per_customer

Cote legit

Meme query mais avec AND w."customerId" NOT IN (SELECT id FROM fraud_ids) et filtre sur la periode AND w."createdAt" >= '$START_DATE' AND w."createdAt" < '$END_DATE'.

D. Regle cooldown tiered (age compte + investissements)

Cote fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
first_wd AS (
  SELECT DISTINCT ON ("customerId")
    "customerId", "createdAt" AS wd_date
  FROM wallet_transactions
  WHERE kind = 'withdrawal' AND status = 'confirmed'
    AND "customerId" IN (SELECT id FROM fraud_ids)
  ORDER BY "customerId", "createdAt" ASC
),
investments AS (
  SELECT fw."customerId", COUNT(pp.*)::int AS cnt
  FROM first_wd fw
  LEFT JOIN primary_purchase pp
    ON (pp.json->>'investorId')::uuid = fw."customerId"
    AND pp."status_view" = 'confirmed'
    AND pp."createdAt_view" < fw.wd_date
  GROUP BY fw."customerId"
),
withdrawals AS (
  SELECT DISTINCT ON (w.id)
    w."customerId",
    EXTRACT(EPOCH FROM (w."createdAt" - t."createdAt")) / 3600 AS delay_h,
    EXTRACT(DAY FROM w."createdAt" - c."createdAt") AS account_age
  FROM wallet_transactions w
  JOIN customers c ON c.id = w."customerId"
  INNER JOIN wallet_transactions t
    ON t."customerId" = w."customerId"
    AND t.kind IN ('topup_card','topup_checkout')
    AND t."createdAt" < w."createdAt"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  ORDER BY w.id, t."createdAt" DESC
),
per_customer AS (
  SELECT w."customerId",
    MIN(w.delay_h) AS min_delay_h,
    MIN(w.account_age) AS min_account_age,
    COALESCE(i.cnt, 0) AS investment_count
  FROM withdrawals w
  LEFT JOIN investments i ON i."customerId" = w."customerId"
  GROUP BY w."customerId", i.cnt
)
SELECT
  -- Mature = age >= 60j OU >= 3 investissements
  COUNT(*) FILTER (WHERE (min_account_age >= 60 OR investment_count >= 3) AND min_delay_h < 48) AS mature_caught_48h,
  COUNT(*) FILTER (WHERE (min_account_age >= 60 OR investment_count >= 3)) AS mature_total,
  -- Jeune = age < 60j ET < 3 investissements
  COUNT(*) FILTER (WHERE (min_account_age < 60 AND investment_count < 3) AND min_delay_h < 168) AS young_caught_7d,
  COUNT(*) FILTER (WHERE (min_account_age < 60 AND investment_count < 3)) AS young_total,
  -- Combine
  COUNT(*) FILTER (WHERE
    ((min_account_age >= 60 OR investment_count >= 3) AND min_delay_h < 48) OR
    ((min_account_age < 60 AND investment_count < 3) AND min_delay_h < 168)
  ) AS tiered_caught,
  COUNT(*) AS total
FROM per_customer

E. Regle velocite topup (tentatives par jour)

Cote fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
topup_bursts AS (
  SELECT "customerId", "createdAt"::date AS topup_day,
    COUNT(*) AS total_attempts,
    COUNT(*) FILTER (WHERE status = 'declined') AS declined
  FROM wallet_transactions
  WHERE kind IN ('topup_card','topup_checkout')
    AND "customerId" IN (SELECT id FROM fraud_ids)
  GROUP BY "customerId", "createdAt"::date
),
per_customer AS (
  SELECT "customerId",
    MAX(total_attempts) AS max_attempts_day,
    MAX(declined) AS max_declined_day
  FROM topup_bursts
  GROUP BY "customerId"
)
SELECT
  COUNT(*) FILTER (WHERE max_attempts_day >= 3) AS caught_3_day,
  COUNT(*) FILTER (WHERE max_attempts_day >= 5) AS caught_5_day,
  COUNT(*) FILTER (WHERE max_attempts_day >= 10) AS caught_10_day,
  COUNT(*) FILTER (WHERE max_declined_day >= 3) AS caught_3_declined_day,
  COUNT(*) FILTER (WHERE max_declined_day >= 5) AS caught_5_declined_day,
  COUNT(*) FILTER (WHERE max_declined_day >= 10) AS caught_10_declined_day,
  COUNT(*) AS total
FROM per_customer

Cote legit (percentiles)

WITH customer_stats AS (
  SELECT "customerId",
    COUNT(*) FILTER (WHERE status = 'confirmed') AS confirmed,
    COUNT(*) FILTER (WHERE status = 'declined') AS declined,
    COUNT(*) AS total
  FROM wallet_transactions
  WHERE kind IN ('topup_card','topup_checkout')
    AND "createdAt" >= '$START_DATE' AND "createdAt" < '$END_DATE'
    AND "customerId" NOT IN (SELECT unnest($FRAUD_IDS::uuid[]))
  GROUP BY "customerId"
  HAVING COUNT(*) FILTER (WHERE status = 'declined') > 0
)
SELECT
  COUNT(*) AS nb_customers_with_declines,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY declined) AS median_declined,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY declined) AS p90_declined,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY declined) AS p95_declined,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY declined) AS p99_declined,
  MAX(declined) AS max_declined
FROM customer_stats

F. Regle cooldown IBAN

Delai entre ajout de l'IBAN et retrait vers cet IBAN. Utilise le lien exact via paymentMethodId (renseigne a 100% sur les withdrawals).

Cote fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
wd_with_iban AS (
  SELECT w."customerId",
    EXTRACT(EPOCH FROM (w."createdAt" - pm."createdAt")) / 3600 AS hours_iban_to_wd
  FROM wallet_transactions w
  JOIN payment_methods pm ON pm.id = w."paymentMethodId"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
),
per_customer AS (
  SELECT "customerId", MIN(hours_iban_to_wd) AS min_h FROM wd_with_iban GROUP BY "customerId"
)
SELECT
  COUNT(*) FILTER (WHERE min_h < 12) AS caught_12h,
  COUNT(*) FILTER (WHERE min_h < 24) AS caught_24h,
  COUNT(*) FILTER (WHERE min_h < 48) AS caught_48h,
  COUNT(*) FILTER (WHERE min_h < 72) AS caught_72h,
  COUNT(*) FILTER (WHERE min_h < 168) AS caught_7d,
  COUNT(*) AS total
FROM per_customer

Cote legit

Meme query avec NOT IN (SELECT id FROM fraud_ids) et filtre periode sur w."createdAt".

G. Regle multi-IBAN / BICs

BICs utilises par les fraudeurs

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
)
SELECT pm.bic, COUNT(DISTINCT pm."customerId") AS nb_fraudsters
FROM fraud_ids f
JOIN payment_methods pm ON pm."customerId" = f.id
WHERE pm.kind = 'iban_withdraw'
GROUP BY pm.bic
ORDER BY nb_fraudsters DESC

IBANs hors-FR

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
)
SELECT
  COUNT(DISTINCT pm."customerId") FILTER (WHERE LEFT(pm.iban, 2) != 'FR') AS nb_with_non_fr_iban,
  COUNT(DISTINCT pm."customerId") AS total_with_iban
FROM fraud_ids f
JOIN payment_methods pm ON pm."customerId" = f.id
WHERE pm.kind = 'iban_withdraw'

H. Matrice de couverture combinee

Combine toutes les regles et compte les combinaisons.

WITH fraud_ids AS (
  SELECT unnest($FRAUD_IDS::uuid[]) AS id
),
r1 AS (
  SELECT DISTINCT ON (w.id) w."customerId",
    EXTRACT(EPOCH FROM (w."createdAt" - t."createdAt")) / 3600 AS delay_h
  FROM wallet_transactions w
  INNER JOIN wallet_transactions t ON t."customerId" = w."customerId"
    AND t.kind IN ('topup_card','topup_checkout') AND t."createdAt" < w."createdAt"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  ORDER BY w.id, t."createdAt" DESC
),
r1_agg AS (SELECT "customerId", MIN(delay_h) AS min_r1 FROM r1 GROUP BY "customerId"),
r3 AS (
  SELECT "customerId", MAX(cnt) AS max_day
  FROM (
    SELECT "customerId", "createdAt"::date, COUNT(*) AS cnt
    FROM wallet_transactions WHERE kind IN ('topup_card','topup_checkout')
      AND "customerId" IN (SELECT id FROM fraud_ids)
    GROUP BY "customerId", "createdAt"::date
  ) sub GROUP BY "customerId"
),
r4 AS (
  SELECT w."customerId",
    MIN(EXTRACT(EPOCH FROM (w."createdAt" - pm."createdAt")) / 3600) AS min_iban_h
  FROM wallet_transactions w
  JOIN payment_methods pm ON pm.id = w."paymentMethodId"
  WHERE w.kind = 'withdrawal' AND w.status = 'confirmed'
    AND w."customerId" IN (SELECT id FROM fraud_ids)
  GROUP BY w."customerId"
),
combined AS (
  SELECT f.id,
    COALESCE(r1a.min_r1 < 48, false) AS hit_cooldown_48h,
    COALESCE(r3.max_day >= 5, false) AS hit_velocity_5day,
    COALESCE(r4.min_iban_h < 24, false) AS hit_iban_24h
  FROM fraud_ids f
  LEFT JOIN r1_agg r1a ON r1a."customerId" = f.id
  LEFT JOIN r3 ON r3."customerId" = f.id
  LEFT JOIN r4 ON r4."customerId" = f.id
)
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE hit_cooldown_48h) AS cooldown_only,
  COUNT(*) FILTER (WHERE hit_velocity_5day) AS velocity_only,
  COUNT(*) FILTER (WHERE hit_iban_24h) AS iban_only,
  COUNT(*) FILTER (WHERE hit_cooldown_48h OR hit_velocity_5day) AS cooldown_or_velocity,
  COUNT(*) FILTER (WHERE hit_cooldown_48h OR hit_iban_24h) AS cooldown_or_iban,
  COUNT(*) FILTER (WHERE hit_cooldown_48h OR hit_velocity_5day OR hit_iban_24h) AS all_three,
  COUNT(*) FILTER (WHERE NOT hit_cooldown_48h AND NOT hit_velocity_5day AND NOT hit_iban_24h) AS missed_all
FROM combined

Adapter les seuils dans les COALESCE(...) selon les regles testees. Ajouter/retirer des CTEs selon les regles evaluees.