Fraud Detection [SQL + Python (Supervised)]

Predict fraudulent transactions using SQL (SQLite) for feature engineering and Python with Logistic Regression for supervised classification.

---

Overview

This project extends the unsupervised version by introducing labeled data and supervised learning. It demonstrates a complete fraud prediction pipeline, from SQL feature generation to model training, evaluation, and visualization.

---

Workflow

• Load labeled data into SQLite
• Run SQL feature engineering

- Compute per-user and daily transaction statistics

• Train Logistic Regression model

- Input: engineered SQL features - Output: fraud probability for each transaction

• Evaluate model performance

- AUC, Precision, Recall, F1-score

• Visualize ROC curve

---

Project Structure

fraud-detection-sql-supervised/
├─ README.md
├─ requirements.txt
├─ data/
│  └─ transactions_labeled.csv
├─ src/
│  ├─ create_db.py
│  ├─ queries.sql
│  ├─ train_supervised.py
│  └─ utils.py
└─ outputs/
   ├─ metrics.json
   ├─ fraud_scores.csv
   ├─ fraud_summary.csv
   └─ charts/
       └─ roc_curve.png

---

Dataset Schema

| Column | Description | |---------|--------------| | tx_id | Transaction ID | | user_id | Unique user identifier | | date | Transaction date | | region | User region | | merchant | Merchant name | | amount | Transaction amount | | label | 1 = Fraudulent, 0 = Legitimate |

---

SQL Feature Engineering

Feature generation reuses the same structure as the unsupervised project.

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,        t.label 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

Model: Logistic Regression

• Trained on labeled transaction data
• Balanced class weights for rare fraud cases
• Evaluated using ROC AUC, precision, recall, and F1-score
• Generates probability scores (fraud_proba) for each transaction

---

Visualization

ROC Curve

The ROC curve shows the trade-off between true positive rate (recall) and false positive rate. A curve closer to the top-left corner indicates stronger predictive performance.

---

Tools & Libraries

| Tool | Purpose | |------|----------| | SQLite | Data storage and feature generation | | Python | ML training and evaluation | | pandas | Data handling | | scikit-learn | Model building and metrics | | matplotlib | Visualization |

---

Usage

Load Data into SQLite

python src/createdb.py --csv data/transactionslabeled.csv --db fraud.db

Train and Evaluate Model

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

---

Outputs

| File | Description | |------|--------------| | metrics.json | Model performance metrics | | fraud_scores.csv | Ranked transactions with fraud probability | | fraud_summary.csv | Aggregated user-level fraud summary | | roc_curve.png | ROC curve visualization |

---

Conclusion

This project demonstrates a complete supervised fraud detection workflow using SQL and Python. It combines data engineering, model training, and evaluation into a single reproducible pipeline suitable for production-ready analytics and portfolio demonstration.