Fraud Detection [SQL + Python (Unsupervised)]

Detect potentially fraudulent bank transactions using SQL (SQLite) for feature engineering and Python for unsupervised anomaly detection with Isolation Forest.

---

Overview

This project demonstrates a practical fraud detection workflow where no labeled data is available. It integrates SQL-based data aggregation with machine learning anomaly detection, showing how data engineers and analysts can uncover unusual transaction patterns in banking or financial systems.

---

Workflow

• Load transaction data into SQLite
• Run SQL feature engineering

- Compute user-level metrics (average amount, total amount, number of transactions) - Compute daily activity (daily totals and transaction counts)

• Apply Isolation Forest to detect anomalies based on aggregated behavioral features
• Generate outputs

- Ranked anomaly scores - Summary tables - Distribution chart

---

Project Structure

fraud-detection-sql-unsupervised/
├─ README.md
├─ requirements.txt
├─ data/
│  └─ transactions.csv
├─ src/
│  ├─ create_db.py
│  ├─ queries.sql
│  ├─ detectfraudunsupervised.py
│  └─ utils.py
└─ outputs/
   ├─ fraud_scores.csv
   ├─ fraud_summary.csv
   └─ charts/
       └─ fraud_distribution.png

---

Dataset Schema

| Column | Description | |---------|--------------| | tx_id | Transaction ID | | user_id | Unique user identifier | | date | Transaction date | | region | User region | | merchant | Merchant name or type | | amount | Transaction amount |

---

SQL Feature Engineering

Feature generation is handled by src/queries.sql. It builds temporary SQL views to calculate user statistics and daily activity.

CREATE TEMP VIEW user_stats AS
SELECT userid, COUNT(*) AS txcount, AVG(amount) AS avgamount, SUM(amount) AS totalamount
FROM transactions
GROUP BY user_id;
CREATE TEMP VIEW daily_user AS SELECT userid, date, COUNT(*) AS dailytx, SUM(amount) AS daily_amount FROM transactions GROUP BY user_id, date;
SELECT t.txid, t.userid, t.date, t.region, t.merchant, t.amount,        us.txcount, us.avgamount, us.total_amount,        COALESCE(du.dailytx, 0) AS dailytx,        COALESCE(du.dailyamount, 0.0) AS dailyamount FROM transactions t LEFT JOIN userstats us ON t.userid = us.user_id LEFT JOIN dailyuser du ON t.userid = du.user_id AND t.date = du.date;

---

Machine Learning

The unsupervised model uses Isolation Forest from scikit-learn.

• Detects outliers based on feature deviation
• Flags top anomalies (typically 2–3% of all transactions)
• Produces a normalized anomaly_score between 0 and 1

---

Visualization

Anomaly Score Distribution

This histogram visualizes the distribution of anomaly scores across transactions. The right-side tail represents potentially fraudulent or irregular activities.

---

Tools & Libraries

| Tool | Purpose | |------|----------| | SQLite | Feature engineering and querying | | Python | Analysis and ML modeling | | pandas | Data manipulation | | scikit-learn | Isolation Forest implementation | | matplotlib | Visualization |

---

Usage

Load Data into SQLite

python src/create_db.py --csv data/transactions.csv --db fraud.db

Run Detection

python src/detectfraudunsupervised.py --db fraud.db --sql src/queries.sql --outdir outputs

---

Outputs

| File | Description | |------|--------------| | fraud_scores.csv | Ranked transactions with anomaly scores | | fraud_summary.csv | User-level fraud summary | | fraud_distribution.png | Histogram of anomaly scores |

---

Conclusion

This project showcases a complete unsupervised anomaly detection pipeline. It demonstrates how SQL + Python can work together to identify rare or irregular financial behaviors, a foundation for fraud prevention and risk analysis systems.