Fraud ring tespiti

Paylasilan cihaz, IP ve kart desenleri GRAPH MATCH ile bulunuyor; risk seviyesi paylasan musteri sayisindan geliyor.

SELECT
    'shared_device'                          AS pattern,
    d.device_id,
    LEFT(d.fingerprint, 12) + N'…'           AS fingerprint,
    d.ip_address,
    COUNT(DISTINCT cd.customer_id)           AS cust_n,
    STRING_AGG(c.full_name, N', ')
        WITHIN GROUP (ORDER BY c.full_name)  AS customers,
    CASE WHEN COUNT(DISTINCT cd.customer_id) >= 4 THEN 'HIGH' ELSE 'MEDIUM' END AS risk_level
FROM   shop.customer_device cd
JOIN   shop.device   d ON d.device_id   = cd.device_id
JOIN   shop.customer c ON c.customer_id = cd.customer_id
GROUP BY d.device_id, d.fingerprint, d.ip_address
HAVING COUNT(DISTINCT cd.customer_id) >= 2
ORDER BY cust_n DESC;
GO

-- ----------------------------------------------------------------------------
-- (2) Aynı IP'yi paylaşan müşteriler
-- ----------------------------------------------------------------------------
SELECT
    'shared_ip'                              AS pattern,
    d.ip_address,
    COUNT(DISTINCT cd.customer_id)           AS cust_n,
    STRING_AGG(c.full_name, N', ')
        WITHIN GROUP (ORDER BY c.full_name)  AS customers,
    CASE WHEN COUNT(DISTINCT cd.customer_id) >= 5 THEN 'HIGH' ELSE 'MEDIUM' END AS risk_level
FROM   shop.customer_device cd
JOIN   shop.device   d ON d.device_id   = cd.device_id
JOIN   shop.customer c ON c.customer_id = cd.customer_id
GROUP BY d.ip_address
HAVING COUNT(DISTINCT cd.customer_id) >= 3
ORDER BY cust_n DESC;
GO

-- ----------------------------------------------------------------------------
-- (3) Aynı kart parmak izini paylaşan müşteriler
-- ----------------------------------------------------------------------------
SELECT
    'shared_card'                            AS pattern,
    LEFT(pm.card_fingerprint, 8) + N'…'      AS card_fp,
    COUNT(DISTINCT pm.customer_id)           AS cust_n,

Tam betik: sql/22-fraud-ring.sql