Как посчитать IP velocity в SQL

Зачем IP velocity

Один IP с десятками signups за час — почти всегда abuse. Это main attack vector против промо/refer-a-friend/free trial. IP velocity check — простой rule, ловит большую долю scripted attacks. Не путать с user velocity (отдельный customer activity).

Окна и метрики

• signups per IP / hour
• transactions per IP / minute
• unique cards per IP / day
• failed login attempts per IP / hour (credential stuffing)

IP velocity в SQL

SELECT
    ip,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '1 minute')  AS last_1_min,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '1 hour')    AS last_1_hour,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '24 hours')  AS last_24h,
    COUNT(DISTINCT user_id) FILTER (WHERE created_at >= NOW() - INTERVAL '24 hours') AS unique_users_24h
FROM signups
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY ip
HAVING COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '1 hour') > 10
ORDER BY last_1_hour DESC;

10+ signups / hour от одного IP — red flag.

По ASN/region

SELECT
    asn,
    asn_org,
    country,
    COUNT(*) AS signups,
    COUNT(DISTINCT ip) AS unique_ips,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT ip), 0) AS signups_per_ip
FROM signups
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY asn, asn_org, country
HAVING COUNT(*) > 100
ORDER BY signups_per_ip DESC;

Datacenter ASN (AWS, DigitalOcean) с высоким signups/ip — botnet.

Bot-сети

Distributed attacks через много IP:

WITH suspicious_window AS (
    SELECT
        DATE_TRUNC('minute', created_at) AS minute,
        COUNT(DISTINCT ip) AS unique_ips,
        COUNT(*) AS signups
    FROM signups
    WHERE created_at >= NOW() - INTERVAL '24 hours'
    GROUP BY DATE_TRUNC('minute', created_at)
    HAVING COUNT(*) > 50  -- общая активность высокая
)
SELECT
    minute,
    unique_ips,
    signups,
    signups::NUMERIC / unique_ips AS signups_per_ip
FROM suspicious_window
ORDER BY signups DESC;

Спайк signups при низком signups_per_ip — много IP, мало per IP = distributed bot.

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

Ошибка 1. Один IP threshold для всех. Corporate NAT — 100+ users за одним IP. Не банят университеты.

Ошибка 2. Не различать residential vs datacenter IPs. Datacenter IPs почти всегда suspicious (no normal user). Residential — норма.

Ошибка 3. IPv6 одинаково с IPv4. IPv6 prefix /64 = whole household. Один user может ротировать addresses.

Ошибка 4. Не учитывать VPN. Legitimate юзеры юзают VPN. Не block automatically.

Ошибка 5. IP geolocation как absolute truth. GeoIP DB врёт на 5-10%. Не block solely on geo-mismatch.

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

• Как посчитать velocity check в SQL
• Как посчитать fraud score в SQL
• Как посчитать device fingerprinting в SQL
• Как посчитать account takeover signals в SQL

FAQ

Какие пороги?

Зависит от продукта. Consumer SaaS: > 20 signups/h — review. B2B: > 5 / h.

NAT — что делать?

Whitelist trusted corporate ranges. Add device fingerprint для disambiguation.

Datacenter detection?

MaxMind, IPHub, ipqualityscore — APIs.

VPN/Tor block?

Не сразу. Legitimate users юзают. Flag, не block.

Real-time check?

В SQL медленно. Кеш в Redis (counter per IP) + flush в DB ежечасно.