Introduction
In an increasingly digital world, detecting transactional fraud has become crucial. While machine learning and graph databases are often hyped, the most reliable tool often remains good old SQL. Let's explore six SQL patterns that will help you effectively spot anomalies in transactions.
1. Transaction Velocity
Velocity is one of the simplest and most powerful signals. When a card is stolen, the fraudster usually tries to drain it before the holder notices. To capture this, you can use an SQL query that groups transactions by cardholder and by hourly buckets:
``sql SELECT cardholder_id, date_trunc('hour', timestamp) AS hour_bucket, count() AS tx_count FROM transactions WHERE timestamp >= current_date - INTERVAL '30 days' GROUP BY 1, 2 HAVING count() > 10; ``
This query allows you to identify cardholders who perform an abnormally high number of transactions in an hour. You can adjust the threshold and time window as needed.
2. Impossible Travel
The idea is simple: if a card is used in two distant locations within a short time frame, it is likely cloned. Here's a query to detect these anomalies:
``sql WITH ordered_tx AS ( SELECT cardholder_id, timestamp, location, LAG(timestamp) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_ts, LAG(location) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_loc FROM transactions ) SELECT cardholder_id, prev_ts AS first_tx, timestamp AS second_tx, prev_loc AS first_location, location AS second_location, EXTRACT(EPOCH FROM (timestamp - prev_ts)) AS time_diff FROM ordered_tx WHERE time_diff < 420 AND prev_loc != location; ``
Here, we check for transactions that occur within seven minutes between different locations.
3. Suspicious Pair Transactions
This involves spotting repeated transactions between the same pair of entities, which may indicate a fraud pattern. Use this query to detect such patterns:
``sql SELECT sender_id, receiver_id, count(*) AS tx_count FROM transactions WHERE date >= current_date - INTERVAL '30 days' GROUP BY sender_id, receiver_id HAVING tx_count > 5; ``
This approach helps you monitor suspicious relationships between two accounts or entities.
4. Unusual Transaction Amount
Atypical amounts can also signal potential fraud. For instance, a very high or very low transaction compared to a user's usual average. Here's how to detect that:
``sql SELECT cardholder_id, amount, avg(amount) OVER (PARTITION BY cardholder_id) AS avg_amount FROM transactions WHERE amount > 2 * avg_amount; ``
This query identifies transactions that are twice the user's average.
5. Suspicious Credit Card Usage
Certain credit cards may be used abnormally, such as being active in geographic areas where the holder never travels. Use this query:
``sql SELECT cardholder_id, location, count(*) AS location_count FROM transactions WHERE date >= current_date - INTERVAL '30 days' GROUP BY cardholder_id, location HAVING location_count > 10; ``
This will help you spot cards excessively used in unusual places.
Conclusion
Detecting transactional fraud doesn't always require sophisticated tools but rather well-thought-out SQL queries. By applying these patterns to your data, you can quickly and effectively identify suspicious behaviors. Let's discuss your project in 15 minutes.