SQL Patterns for Transaction Fraud Detection

The financial world of 2026 is a battlefield, and transaction fraud is the enemy that never sleeps. With sophisticated fraudsters leveraging AI and increasingly intricate schemes, businesses face astronomical losses that can cripple their operations and erode customer trust. While Machine Learning (ML) models often grab the headlines in fraud detection, the bedrock of any robust anti-fraud system remains the well-structured and highly optimized SQL queries running directly on your transactional databases.

As an expert in database performance and design, I’ve seen firsthand how intelligently designed SQL patterns can act as the first, fastest, and often most effective line of defense. We’re talking about real-time or near real-time detection, leveraging the power of modern relational databases like PostgreSQL 17+, SQL Server 2025+, and Oracle 23c to spot anomalies before they escalate. Forget slow batch processes; today’s fraud detection needs immediate action.

In this deep dive, we’ll explore essential SQL patterns that can help you identify suspicious activity, complete with practical code examples, performance insights, and best practices relevant for 2026.

The Fraudster’s Playbook vs. Your SQL Defense

Fraudsters are agile. They exploit vulnerabilities through various tactics: rapid small purchases (card testing), large out-of-character transactions, account takeovers, and even coordinated attacks across multiple accounts. Your SQL defense needs to anticipate these patterns. We’re going to use advanced window functions, intelligent indexing, and an understanding of transaction characteristics to build a formidable wall against illicit activities.

Let’s assume a transactions table (PostgreSQL/SQL Server syntax, easily adaptable) with critical columns:

CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Or INT identity
    user_id UUID NOT NULL,
    card_id UUID, -- NULL for non-card transactions
    amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,
    transaction_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    device_id UUID,
    ip_address INET,
    latitude DECIMAL(9, 6),
    longitude DECIMAL(9, 6),
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' -- PENDING, APPROVED, DECLINED, FRAUD_REVIEW
);

CREATE INDEX idx_transactions_user_ts ON transactions (user_id, transaction_timestamp DESC);
CREATE INDEX idx_transactions_card_ts ON transactions (card_id, transaction_timestamp DESC);
CREATE INDEX idx_transactions_ts ON transactions (transaction_timestamp DESC);
CREATE INDEX idx_transactions_lat_lon ON transactions (latitude, longitude);

Core SQL Patterns for Detection

1. Velocity Checks: The “Too Fast, Too Furious” Transactions

Problem: A single user or card making an unusually high number of transactions within a short timeframe. This often indicates card testing, stolen credentials, or account takeover.

SQL Solution (Using Window Functions with RANGE):

Modern SQL’s window functions, especially with the RANGE clause, are incredibly powerful for this. We can count transactions within a moving time window for each user or card.

SELECT
    t.transaction_id,
    t.user_id,
    t.card_id,
    t.amount,
    t.transaction_timestamp,
    t.ip_address,
    COUNT(t.transaction_id) OVER (
        PARTITION BY t.user_id
        ORDER BY t.transaction_timestamp
        RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
    ) AS transactions_in_5min_user,
    COUNT(t.transaction_id) OVER (
        PARTITION BY t.card_id
        ORDER BY t.transaction_timestamp
        RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
    ) AS transactions_in_5min_card
FROM
    transactions t
WHERE
    t.status = 'APPROVED' -- Focus on successful transactions for velocity
QUALIFY -- SQL Server 2025+, Oracle 23c equivalent for WHERE on window functions
    (transactions_in_5min_user > 5 AND t.user_id IS NOT NULL) OR
    (transactions_in_5min_card > 3 AND t.card_id IS NOT NULL);

-- For PostgreSQL 17+, you'd wrap this in a CTE or subquery:
WITH TransactionVelocity AS (
    SELECT
        t.transaction_id,
        t.user_id,
        t.card_id,
        t.amount,
        t.transaction_timestamp,
        t.ip_address,
        COUNT(t.transaction_id) OVER (
            PARTITION BY t.user_id
            ORDER BY t.transaction_timestamp
            RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
        ) AS transactions_in_5min_user,
        COUNT(t.transaction_id) OVER (
            PARTITION BY t.card_id
            ORDER BY t.transaction_timestamp
            RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW
        ) AS transactions_in_5min_card
    FROM
        transactions t
    WHERE
        t.status = 'APPROVED'
)
SELECT *
FROM TransactionVelocity
WHERE
    (transactions_in_5min_user > 5 AND user_id IS NOT NULL) OR
    (transactions_in_5min_card > 3 AND card_id IS NOT NULL);

Explanation: The RANGE BETWEEN ... PRECEDING AND CURRENT ROW clause dynamically defines a time window. For each transaction, it looks back 5 minutes (including the current transaction) and counts all transactions within that period for the same user or card. This is highly efficient, especially with the idx_transactions_user_ts and idx_transactions_card_ts indexes.

Performance: Crucially, these queries benefit immensely from compound indexes on (user_id, transaction_timestamp) and (card_id, transaction_timestamp). For near real-time detection, consider storing these velocity scores in a separate, frequently updated table or a materialized view, refreshing incrementally or on a schedule.

2. Geolocation Anomalies: The “Teleporting” User

Problem: A user makes transactions from vastly different geographic locations in an impossibly short time. This is a strong indicator of account compromise or stolen card usage.

SQL Solution (Using LAG for sequential comparison):

WITH UserTransactions AS (
    SELECT
        t.transaction_id,
        t.user_id,
        t.transaction_timestamp,
        t.latitude,
        t.longitude,
        LAG(t.latitude) OVER (PARTITION BY t.user_id ORDER BY t.transaction_timestamp) AS prev_latitude,
        LAG(t.longitude) OVER (PARTITION BY t.user_id ORDER BY t.transaction_timestamp) AS prev_longitude,
        LAG(t.transaction_timestamp) OVER (PARTITION BY t.user_id ORDER BY t.transaction_timestamp) AS prev_timestamp
    FROM
        transactions t
    WHERE
        t.latitude IS NOT NULL AND t.longitude IS NOT NULL
)
SELECT
    ut.transaction_id,
    ut.user_id,
    ut.transaction_timestamp,
    ut.latitude,
    ut.longitude,
    -- Calculate distance (simplified for illustration; Haversine for accuracy)
    -- This example uses Euclidean approximation for small distances, often sufficient for initial flags
    SQRT(
        POWER(69.1 * (ut.latitude - ut.prev_latitude), 2) +
        POWER(69.1 * COS(RADIANS(ut.prev_latitude)) * (ut.longitude - ut.prev_longitude), 2)
    ) AS distance_miles,
    EXTRACT(EPOCH FROM (ut.transaction_timestamp - ut.prev_timestamp)) / 60 AS time_diff_minutes
FROM
    UserTransactions ut
WHERE
    ut.prev_latitude IS NOT NULL AND ut.prev_longitude IS NOT NULL AND ut.prev_timestamp IS NOT NULL
    AND SQRT(
        POWER(69.1 * (ut.latitude - ut.prev_latitude), 2) +
        POWER(69.1 * COS(RADIANS(ut.prev_latitude)) * (ut.longitude - ut.prev_longitude), 2)
    ) > 500 -- Distance greater than 500 miles
    AND EXTRACT(EPOCH FROM (ut.transaction_timestamp - ut.prev_timestamp)) / 60 < 60; -- Within 60 minutes

Explanation: LAG retrieves values from the previous row within the user’s partition, ordered by timestamp. We then compare the current transaction’s location and timestamp with the previous one. The distance calculation here is a simplified Euclidean approximation; for high accuracy, especially across longitudes, use the Haversine formula (or leverage PostGIS functions like ST_Distance if on PostgreSQL).

Performance: The idx_transactions_user_ts index is critical here. Using a geographical index (like a PostGIS GIST index on a geometry column) can significantly speed up spatial queries if you have more complex needs.

3. First-Time User, High-Value Purchase: The “New Account, Big Spend” Red Flag

Problem: A newly registered user attempts a very large transaction, often immediately after account creation. This profile is common for fraudsters testing stolen credentials or credit card numbers.

SQL Solution:

SELECT
    t.transaction_id,
    t.user_id,
    t.amount,
    t.transaction_timestamp,
    u.registration_timestamp,
    DENSE_RANK() OVER (PARTITION BY t.user_id ORDER BY t.transaction_timestamp) AS user_transaction_rank,
    (t.transaction_timestamp - u.registration_timestamp) AS time_since_registration
FROM
    transactions t
JOIN
    users u ON t.user_id = u.user_id
WHERE
    t.amount > 1000.00 -- High-value threshold
    AND (t.transaction_timestamp - u.registration_timestamp) < INTERVAL '1' HOUR -- Within 1 hour of registration
    AND DENSE_RANK() OVER (PARTITION BY t.user_id ORDER BY t.transaction_timestamp) = 1; -- First transaction

Explanation: We join transactions with a users table (assuming it has user_id and registration_timestamp). We then use a combination of direct time comparison and DENSE_RANK to identify the user’s very first transaction and check if it meets the high-value and immediate-post-registration criteria.

Performance: Indexes on (transactions.user_id, transactions.transaction_timestamp) and (users.user_id, users.registration_timestamp) are essential for efficient joining and window function processing.

4. Small, Repeated Transactions: The “Card Tester” Pattern

Problem: Fraudsters often test stolen card numbers by making numerous small, often identical, transactions to see which cards are active. These transactions are typically below a certain threshold to avoid triggering immediate large-transaction alerts.

SQL Solution:

This is similar to velocity checks but with an added condition on amount and potentially currency.

WITH CardTestAttempts AS (
    SELECT
        t.transaction_id,
        t.card_id,
        t.amount,
        t.transaction_timestamp,
        COUNT(t.transaction_id) OVER (
            PARTITION BY t.card_id
            ORDER BY t.transaction_timestamp
            RANGE BETWEEN INTERVAL '10' MINUTE PRECEDING AND CURRENT ROW
        ) AS recent_transactions_count,
        SUM(t.amount) OVER (
            PARTITION BY t.card_id
            ORDER BY t.transaction_timestamp
            RANGE BETWEEN INTERVAL '10' MINUTE PRECEDING AND CURRENT ROW
        ) AS recent_transactions_sum
    FROM
        transactions t
    WHERE
        t.status = 'APPROVED'
        AND t.amount <= 5.00 -- Amount less than or equal to $5.00
)
SELECT
    cta.transaction_id,
    cta.card_id,
    cta.amount,
    cta.transaction_timestamp,
    cta.recent_transactions_count,
    cta.recent_transactions_sum
FROM
    CardTestAttempts cta
WHERE
    cta.recent_transactions_count >= 10 -- At least 10 small transactions
    AND cta.recent_transactions_sum <= 20.00; -- Total sum also low, indicating testing

Explanation: We partition by card_id and look for multiple (e.g., 10+) small transactions (e.g., $5.00 or less) within a short time window (e.g., 10 minutes). The recent_transactions_sum adds another layer of validation to ensure it’s not a legitimate burst of activity involving slightly larger, but still small, amounts.

Performance: Indexing (card_id, transaction_timestamp, amount) is paramount here. The filter on amount in the WHERE clause before the window function can significantly reduce the dataset processed by the window, making it faster.

Beyond Basic Patterns: Advanced Considerations

While these patterns are powerful, 2026 demands more.

• Feature Engineering for ML: These SQL-derived values (transactions_in_5min_user, distance_miles, time_since_registration) are directly usable as features for your ML fraud models. SQL becomes your most powerful feature engineering tool.
• Graph Databases (Built into SQL): Databases like Oracle 23c and SQL Server 2022+ are integrating graph capabilities directly into SQL. Imagine identifying fraud rings by querying connections between users, devices, or IPs that have interacted with previously flagged accounts.
• Real-time Processing with Materialized Views: For frequently accessed patterns, consider materialized views. Databases offer incremental refresh capabilities (e.g., REFRESH MATERIALIZED VIEW CONCURRENTLY in PostgreSQL, or change data capture for custom refreshes) to keep your aggregated fraud features fresh without blocking reads.

Troubleshooting & Performance Pitfalls

1. Lack of Proper Indexing: The most common and devastating mistake. Without (user_id, transaction_timestamp) or (card_id, transaction_timestamp) indexes, window functions will perform full table scans, grinding your system to a halt.
2. Overly Complex Window Definitions: While powerful, very large RANGE windows or multiple complex PARTITION BY clauses can be CPU-intensive. Profile your queries and optimize window sizes.
3. Inefficient Date/Time Operations: Avoid casting timestamps to strings or using functions that prevent index usage (e.g., DATE_TRUNC on the indexed column itself in the WHERE clause, unless the database can optimize it). Keep comparisons simple with INTERVAL or direct timestamp arithmetic.
4. Data Volume: For extremely high-volume transaction systems, you might need to combine these patterns with stream processing technologies (like Apache Flink SQL or ksqlDB) that can compute window functions over continuous data streams before it even hits your primary transactional database. However, for many enterprises, direct database SQL remains sufficient and often simpler to manage.

Actionable Takeaways

• Embrace Window Functions: They are your most powerful tool for analyzing sequential and aggregated behavior.
• Index Strategically: Compound indexes on entity ID and timestamp ((user_id, transaction_timestamp)) are non-negotiable.
• Start Simple, Iterate: Begin with basic velocity and anomaly checks, then build complexity.
• Monitor and Tune: Fraud patterns evolve. Regularly review your SQL queries, performance, and the effectiveness of your detection thresholds.
• Integrate with ML: SQL is excellent for generating features for more advanced ML models.

By mastering these SQL patterns, you empower your fraud detection systems to be faster, smarter, and more resilient. The battle against fraud is ongoing, but with robust SQL, you’ll be well-equipped to win.

---

Discussion Questions for Readers:

1. What other transaction characteristics (e.g., device fingerprints, browser headers, specific item types) do you find most valuable to incorporate into SQL-based fraud patterns?
2. How do you manage the trade-off between real-time detection performance and the computational cost of running complex SQL patterns on your primary transactional database? Do you offload to a separate analytical store or rely on materialized views?