SQL for Financial Transaction Analysis: Fraud Detection with Recursive CTEs

Financial Transaction Fraud Detection

Banking systems process thousands of transactions per second. Detecting money laundering, circular transactions, and suspicious patterns requires powerful SQL techniques.

1. Tracing Transaction Trees with Recursive CTEs

Find the full origin chain of a suspicious transaction — who paid whom, and who paid them, back to the source.

-- Recursive CTE to trace transaction tree up to 5 levels deep
WITH RECURSIVE transaction_tree AS (
    -- Anchor: Start with the suspicious transaction
    SELECT
        sender_id,
        receiver_id,
        amount,
        1 AS depth
    FROM transactions
    WHERE transaction_id = 'suspicious-12345'

    UNION ALL

    -- Recursive: Find who the receiver paid next
    SELECT
        t.sender_id,
        t.receiver_id,
        t.amount,
        tt.depth + 1
    FROM transactions t
    INNER JOIN transaction_tree tt ON t.sender_id = tt.receiver_id
    WHERE tt.depth < 5
)
SELECT * FROM transaction_tree ORDER BY depth, amount DESC;

2. Finding Circular Transaction Patterns

Detounce circular money movement (A → B → C → A) which is a red flag for money laundering.

WITH RECURSIVE path_cte AS (
    -- Start with any transaction
    SELECT
        t1.sender_id AS start_node,
        t1.receiver_id AS current_node,
        ARRAY[t1.sender_id, t1.receiver_id] AS path,
        2 AS depth
    FROM transactions t1

    UNION ALL

    SELECT
        p.start_node,
        t.receiver_id,
        path || t.receiver_id,
        p.depth + 1
    FROM path_cte p
    JOIN transactions t ON p.current_node = t.sender_id
    WHERE NOT t.receiver_id = ANY(path)  -- Don't close the loop yet
    AND p.depth < 6
)
SELECT DISTINCT start_node, current_node
FROM path_cte
WHERE depth >= 3
AND start_node = current_node;  -- Found a circle!

3. Daily Transaction Volume Spikes

Detect abnormal spikes in transaction volume — could indicate card testing or fraud bursts.

SELECT
    DATE(transaction_timestamp) AS txn_date,
    COUNT(*) AS txn_count,
    AVG(COUNT(*)) OVER (ORDER BY DATE(transaction_timestamp) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg,
    CASE WHEN COUNT(*) > AVG(COUNT(*)) OVER (ORDER BY DATE(transaction_timestamp) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) * 2
         THEN 'SPIKE' ELSE 'NORMAL' END AS status
FROM transactions
WHERE transaction_timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(transaction_timestamp)
ORDER BY txn_date DESC;

Key Takeaways for Production

• Recursive CTEs are the go-to technique for traversing hierarchical or graph-like data in a single query — perfect for transaction trees and org charts.
• Use ARRAY to track the path taken and prevent infinite loops by checking NOT ... = ANY(path).
• Window functions with ROWS BETWEEN enable fast moving averages and anomaly detection without self-joins.
• Always index sender_id, receiver_id, and transaction_timestamp for performance on high-throughput tables.