Büyük sorgu 5: bankada şüpheli işlem kural motoru

Soru

Ilgaz Bank uyum ekibi her sabah incelenecek hesapların listesini istiyor. Kurallar iş biriminden gelir ve sık değişir; bu yüzden sorgu, her kuralın ayrı bir blok olduğu ve kolayca eklenip çıkarılabildiği bir yapıda yazılır.

Sorgu

WITH harcama AS (        -- 0) Kart harcamaları, tutar pozitif
  SELECT t.txn_id, t.account_id, t.txn_time, -t.amount AS tutar, t.merchant_category AS kat,
         CAST(strftime('%H', t.txn_time) AS INTEGER) AS saat
  FROM bank_txns t
  WHERE t.txn_type = 'kart_harcama'
),
ort AS (                 -- 1) Hesabın kendi ortalaması (kişiye göre normal)
  SELECT account_id, AVG(tutar) AS ort_tutar, COUNT(*) AS adet
  FROM harcama
  GROUP BY account_id
),
k1 AS (                  -- Kural 1: gece 01–05 arası 20.000 TL üzeri harcama
  SELECT account_id, txn_id, 'gece_yuksek' AS kural, 3 AS puan
  FROM harcama
  WHERE saat BETWEEN 1 AND 4 AND tutar > 20000
),
k2 AS (                  -- Kural 2: hesabın kendi ortalamasının 8 katından büyük harcama
  SELECT h.account_id, h.txn_id, 'ortalama_8x' AS kural, 2 AS puan
  FROM harcama h
  JOIN ort o ON o.account_id = h.account_id
  WHERE o.adet >= 5 AND h.tutar > 8 * o.ort_tutar
),
k3 AS (                  -- Kural 3: aynı hesapta 10 dakika içinde art arda e-ticaret harcaması
  SELECT DISTINCT h.account_id, h.txn_id, 'hizli_tekrar' AS kural, 1 AS puan
  FROM harcama h
  JOIN harcama p ON p.account_id = h.account_id AND p.txn_id <> h.txn_id
                AND p.kat = 'e_ticaret'
                AND p.txn_time <= h.txn_time
                AND p.txn_time >= datetime(h.txn_time, '-10 minutes')
  WHERE h.kat = 'e_ticaret'
),
alarm AS (               -- 2) Tüm kuralları alt alta topla
  SELECT * FROM k1 UNION ALL SELECT * FROM k2 UNION ALL SELECT * FROM k3
),
hesap AS (               -- 3) Hesap başına toplam risk puanı ve tetiklenen kurallar
  SELECT account_id,
         SUM(puan) AS risk,
         COUNT(DISTINCT txn_id) AS supheli_islem,
         COUNT(DISTINCT kural) AS kural_sayisi
  FROM alarm
  GROUP BY account_id
)
SELECT h.account_id, c.full_name, c.segment, h.risk, h.supheli_islem, h.kural_sayisi,
       DENSE_RANK() OVER (ORDER BY h.risk DESC) AS oncelik
FROM hesap h
JOIN bank_accounts a ON a.account_id = h.account_id
JOIN bank_customers c ON c.customer_id = a.customer_id
ORDER BY oncelik, h.account_id
LIMIT 15;

Adım adım

Neden bu yapı?

• Aynı şema: Her kural CTE'si aynı dört sütunu döndürür; UNION ALL bu sayede çalışır. Yeni bir kural, yeni bir CTE ve alarm satırına bir UNION ALL SELECT * FROM k4 eklemekten ibarettir.
• Kişiye göre normal: Kural 2 sabit bir eşik yerine hesabın kendi ortalamasını kullanır; büyük harcama yapan bir müşteri her harcamasında alarm üretmez.
• UNION ALL, UNION değil: Aynı işlem iki kurala takılırsa iki satır olmalıdır; puanlar toplanır. UNION tekrar eden satırları silerek puanı düşürürdü.