SQL patterns I use to catch transaction fraud
7 hours ago
- #Transaction Analysis
- #Data Patterns
- #SQL Fraud Detection
- SQL is the primary tool for fraud detection in transaction data, not ML or graph databases.
- Six SQL patterns detect fraud: velocity, impossible travel, amount anomalies, suspicious merchants, off-hours, and window functions for chained signals.
- Velocity detects rapid transactions to spot stolen cards; sliding windows and thresholds are tunable.
- Impossible travel identifies cloned cards via distant, quick successive transactions using distance/speed calculations.
- Amount anomalies flag round or just-below-threshold amounts like $1.00 or $99.99, common in card testing or rule evasion.
- Suspicious merchants pinpoint compromised terminals by spikes in unique cards or spending relative to baselines.
- Off-hours analysis flags transactions outside a cardholder's habitual spending times, requiring historical data.
- Window functions enable composable fraud rules, speeding up detection by allowing SQL-based hypothesis testing.
- Combining multiple patterns reduces false positives; start with velocity for new fraud detection efforts.
- Considerations include NULL handling, false positives, privacy, cost optimization, and feedback loops for tuning.