Where SQL stops a bad transaction.
Practice the patterns fraud analysts run every day.

3-sigma transaction outliers

Compute the mean and standard deviation of transaction amounts, then flag any transaction more than 3 standard deviations above the mean. The CTE-plus-CROSS-JOIN pattern is the SQL equivalent of scipy.stats.zscore.

WITH stats AS (
  SELECT AVG(amount) AS mu,
         SQRT(AVG((amount - (SELECT AVG(amount) FROM transactions))*
              (amount - (SELECT AVG(amount) FROM transactions)))) AS sigma
  FROM transactions
)
SELECT t.* FROM transactions t CROSS JOIN stats s
WHERE t.amount > s.mu + 3 * s.sigma;

5+ transactions in 5 minutes

Self-join transactions on account_id where the timestamp difference is under 5 minutes. julianday() * 86400 converts SQLite date math into seconds. Group, count, threshold.

SELECT a.account_id, COUNT(b.txn_id) AS burst_n
FROM transactions a
JOIN transactions b
  ON a.account_id = b.account_id
 AND b.txn_at BETWEEN a.txn_at
   AND DATETIME(a.txn_at, '+5 minutes')
GROUP BY a.account_id, a.txn_at
HAVING COUNT(b.txn_id) >= 5;

Geographic mismatch via LAG

Use the LAG window function to get the previous transaction's location for the same account, then compute distance via a degree-distance proxy (~111km per degree). Flag pairs that travelled faster than physically possible.

WITH seq AS (
  SELECT account_id, txn_at, lat, lng,
    LAG(lat) OVER (PARTITION BY account_id ORDER BY txn_at) AS prev_lat,
    LAG(lng) OVER (PARTITION BY account_id ORDER BY txn_at) AS prev_lng,
    LAG(txn_at) OVER (PARTITION BY account_id ORDER BY txn_at) AS prev_at
  FROM transactions
)
SELECT * FROM seq
WHERE SQRT((lat-prev_lat)*(lat-prev_lat) +
            (lng-prev_lng)*(lng-prev_lng)) * 111 > 500
  AND (julianday(txn_at) - julianday(prev_at)) * 1440 < 25;

Shared device fingerprint

Self-join accounts where they share a device_fingerprint, with the a.account_id < b.account_id trick to dedupe pairs. This catches coordinated rings that try to evade per-account limits.

SELECT a.account_id AS account_a,
       b.account_id AS account_b,
       a.device_fingerprint
FROM accounts a
JOIN accounts b
  ON a.device_fingerprint = b.device_fingerprint
 AND a.account_id < b.account_id
ORDER BY a.device_fingerprint;

Recursive CTE walk

Chargebacks reference earlier chargebacks via related_chargeback_id. A recursive CTE walks the chain with a depth guard so you don't infinite-loop on cycles. This is the SQL pattern fraud investigators use to map a coordinated dispute network.

WITH RECURSIVE chain AS (
  -- anchor: every chargeback as a possible root
  SELECT chargeback_id, related_chargeback_id, 0 AS depth
  FROM chargebacks
  WHERE related_chargeback_id IS NULL
  UNION ALL
  SELECT c.chargeback_id, c.related_chargeback_id, ch.depth + 1
  FROM chargebacks c
  JOIN chain ch ON c.related_chargeback_id = ch.chargeback_id
  WHERE ch.depth < 5   -- safety guard
)
SELECT * FROM chain WHERE depth > 0 ORDER BY depth DESC;

FDIC bank risk patterns

7 additional challenges adapt the same statistical techniques to bank-level data — NPL anomalies (2-sigma), tier-1 capital floor violations, ROA outliers via OFFSET percentile, geographic concentration risk. Same SQL muscle, different domain.