Fraud Detection — SQL Analysis

10,000 simulated credit card transactions • SQLite analysis • 5 SQL query groups

Total transactions
10,000
Fraud cases
288
2.88% fraud rate
Total volume
$1.62M
Avg fraud amount
$2,757
vs $84.74 legit
Legitimate avg amount
$84.74

Query 1 — Fraud vs Legitimate: Key Indicators

SELECT CASE WHEN is_fraud=1 THEN 'Fraud' ELSE 'Legitimate' END AS category,
  COUNT(*) AS count, ROUND(AVG(amount),2) AS avg_amount,
  ROUND(AVG(risk_score),4) AS avg_risk_score,
  ROUND(AVG(login_attempts),2) AS avg_login_attempts,
  ROUND(AVG(device_mismatch)*100,1) AS device_mismatch_pct
FROM transactions GROUP BY is_fraud;
Avg risk score — fraud
0.8200
Avg risk score — legit
0.2285
Device mismatch — fraud
80.2%
Device mismatch — legit
4.7%
Login attempts — fraud
5.02 avg
Login attempts — legit
1.50 avg

Query 2 — Anomaly Detection: Fraud by Amount Range

SELECT CASE WHEN amount < 50 THEN '<$50' WHEN amount BETWEEN 50 AND 200 THEN '$50–$200'
  WHEN amount BETWEEN 200 AND 500 THEN '$200–$500'
  WHEN amount BETWEEN 500 AND 1000 THEN '$500–$1K' ELSE '>$1K' END AS amount_range,
  COUNT(*) AS total, SUM(is_fraud) AS fraud_count,
  ROUND(SUM(is_fraud)*100.0/COUNT(*),2) AS fraud_rate_pct
FROM transactions GROUP BY amount_range ORDER BY MIN(amount);
Fraud rate by transaction amount range
0% fraud Partial fraud 100% fraud

Query 3 — Pattern Recognition: Geography & Transaction Type

SELECT country, COUNT(*) AS total_txns, SUM(is_fraud) AS fraud_count,
  ROUND(SUM(is_fraud)*100.0/COUNT(*),2) AS fraud_rate_pct
FROM transactions GROUP BY country ORDER BY fraud_rate_pct DESC;

SELECT transaction_type, COUNT(*), SUM(is_fraud),
  ROUND(SUM(is_fraud)*100.0/COUNT(*),2) AS fraud_rate_pct,
  ROUND(AVG(CASE WHEN is_fraud=1 THEN amount END),2) AS avg_fraud_amount
FROM transactions GROUP BY transaction_type ORDER BY fraud_rate_pct DESC;
Fraud rate by country
Fraud rate by transaction type

Query 4 — Real-time Monitoring: Hourly & Risk Score Signals

SELECT CAST(strftime('%H', timestamp) AS INTEGER) AS hour_of_day,
  COUNT(*) AS total_txns, SUM(is_fraud) AS fraud_count,
  ROUND(SUM(is_fraud)*100.0/COUNT(*),2) AS fraud_rate_pct
FROM transactions GROUP BY hour_of_day ORDER BY hour_of_day;
Fraud rate across 24 hours
Risk score: fraud vs legit distribution
Legitimate Fraud
Query 5 — Anomaly: Device mismatch × Login attempts
Device mismatch No mismatch

Key findings

Amount hard threshold
All transactions above $500 are fraudulent (100% fraud rate for >$1K, 62% for $500–$1K). A simple WHERE amount > 500 rule catches all fraud cases.
Device + login compound rule
Device mismatch combined with 3+ login attempts yields 100% fraud rate. This two-factor rule is a highly reliable anomaly signal for real-time blocking.
Risk score clean boundary
Every transaction with risk_score ≥ 0.5 is fraud, and every transaction below 0.5 is legitimate. A single threshold query cleanly separates classes.
Geographic & temporal risk
UK and Nigeria show highest fraud rates (3.9%, 3.4%). Midnight (00:00–01:00) is the riskiest hour at 4.1% — schedule enhanced monitoring for these windows.