Как посчитать fraud score в SQL

Зачем fraud score

Fraud score — числовая оценка риска, что транзакция / signup / login — мошеннические. Правила-based scoring дешевле и интерпретируемее ML-модели, особенно на старте. Для прод-систем обычно комбо: правила + ML. Score > threshold → блок / manual review.

Сигналы

Типичные сигналы:

• new device (никогда не видели)
• new IP, новый ASN
• velocity (много транзакций за минуту)
• mismatch billing vs IP geography
• card BIN из high-fraud страны
• email domain disposable (10minutemail и т.п.)
• name/email looks generated

Score в SQL

Каждому сигналу — вес. Суммируем:

WITH signals AS (
    SELECT
        t.transaction_id,
        t.user_id,
        t.amount,
        CASE WHEN NOT EXISTS (
            SELECT 1 FROM user_devices WHERE user_id = t.user_id AND device_id = t.device_id
        ) THEN 30 ELSE 0 END AS new_device_score,
        CASE WHEN NOT EXISTS (
            SELECT 1 FROM user_ips WHERE user_id = t.user_id AND ip = t.ip
        ) THEN 20 ELSE 0 END AS new_ip_score,
        CASE WHEN (
            SELECT COUNT(*) FROM transactions
            WHERE user_id = t.user_id
              AND created_at >= t.created_at - INTERVAL '1 hour'
        ) > 5 THEN 25 ELSE 0 END AS velocity_score,
        CASE WHEN t.billing_country <> t.ip_country THEN 15 ELSE 0 END AS geo_mismatch_score,
        CASE WHEN t.email_domain IN ('10minutemail.com', 'mailinator.com') THEN 40 ELSE 0 END AS disposable_email_score
    FROM transactions t
    WHERE t.created_at >= CURRENT_DATE - INTERVAL '1 day'
)
SELECT
    transaction_id,
    user_id,
    amount,
    new_device_score + new_ip_score + velocity_score + geo_mismatch_score + disposable_email_score AS fraud_score
FROM signals
ORDER BY fraud_score DESC;

Score 0-130 (max все сигналы). 50+ — review. 80+ — block.

Threshold для блока

WITH scored AS (
    SELECT transaction_id, fraud_score FROM fraud_scoring
)
SELECT
    fraud_score,
    CASE
        WHEN fraud_score >= 80 THEN 'block'
        WHEN fraud_score >= 50 THEN 'review'
        WHEN fraud_score >= 30 THEN 'flag'
        ELSE 'pass'
    END AS action,
    COUNT(*) AS transactions
FROM scored
GROUP BY 1, 2
ORDER BY 1 DESC;

Calibrate threshold по precision/recall на размеченных данных.

Валидация

Сравните score с реальными chargeback / refund / manually labelled fraud:

SELECT
    CASE
        WHEN fraud_score >= 80 THEN 'block'
        WHEN fraud_score >= 50 THEN 'review'
        ELSE 'pass'
    END AS predicted,
    SUM(CASE WHEN actual_fraud THEN 1 ELSE 0 END) AS true_positives,
    SUM(CASE WHEN NOT actual_fraud THEN 1 ELSE 0 END) AS false_positives,
    SUM(CASE WHEN actual_fraud THEN 1 ELSE 0 END)::NUMERIC
    / NULLIF(COUNT(*), 0) AS precision
FROM fraud_validation
GROUP BY 1;

Block precision 80%+ — нормально. 50% — много false positives, лучше понизить threshold.

Частые ошибки

Ошибка 1. Все веса равные. Disposable email — strong signal (90% fraud). New device — weak. Веса нужно подбирать по data.

Ошибка 2. Игнорировать legit edge cases. Командировка → новый IP/device → high score, но это легитимный customer. Whitelist trusted.

Ошибка 3. Score без time decay. Транзакция 6 месяцев назад с low score → safe. Сегодня тот же score → risky. Use rolling baseline.

Ошибка 4. Считать на маленькой выборке. Fraud rate обычно < 0.5%. Нужны 10k+ транзакций для валидации.

Ошибка 5. Использовать только rules. В прод обязательно ML на top of rules для catch sophisticated fraud.

Связанные темы

• Как посчитать fraud rate в SQL
• Как посчитать chargeback rate в SQL
• Как посчитать precision-recall в SQL
• Как посчитать z-score outliers в SQL

FAQ

Rules vs ML?

Rules — fast start, interpretable. ML — catch new patterns. Прод обычно гибрид.

Какие веса?

Калибровать по historical data. Disposable email 40-50, new device 20-30.

Threshold какой?

50-80 для review, 80+ для block. Зависит от false positive cost.

Fraud rate норма?

E-com 0.5-2%. SaaS 0.1-0.5%. Crypto/gambling — 5-20%.

Latency важна?

Да, real-time scoring. SQL может быть медленным — кешируйте signals.