Fintech Fraud Detection — SQL Server

A fraud detection system built entirely in T-SQL on SQL Server, using the PaySim mobile-money dataset. It identifies three distinct fraud patterns using window functions, self-joins, and statistical anomaly detection — no external ML libraries, just SQL.

Why this project

Most portfolio SQL projects stop at GROUP BY and SUM(). This one goes further: it detects behavioral fraud patterns — the kind of logic a real fraud/risk engineering team would build directly into a data pipeline.

The dataset

PaySim — a published, research-grade simulation of mobile money transactions, calibrated on real fraud statistics from an African mobile money service. ~6.3 million transactions, including 8,213 labeled fraud cases.

Important note on enrichment: PaySim does not include location data. To enable geo-based fraud detection (impossible travel), a synthetic Cities table with 10 Indian cities and coordinates was added, and each user/transaction was assigned a city. This is clearly a synthetic addition, not real location data — documented here for transparency, the same way a real data engineering team would document an enrichment layer.

Schema

Table	Purpose
StagingTransactions	Raw landing zone for the CSV — loose types, no constraints, accepts anything
Users	Clean, deduplicated user list (UserID, OriginalUserCode, HomeCityID)
Transactions	Clean transaction records with proper types, foreign keys, and enriched TransactionCityID
Cities	Synthetic reference table (name, country, latitude, longitude)
FlaggedTransactions (view)	Combined output of all three detection rules

Detection logic

1. Velocity check (window functions)

Flags a transaction that occurs less than 5 minutes after the same user's previous transaction — a classic account-takeover / rapid-drain signal. Uses LAG() OVER (PARTITION BY SenderUserID ORDER BY TransactionTime) to compare each transaction to that same user's prior one, without a self-join.

2. Impossible travel (self-join)

Flags the same user transacting in two different cities within 60 minutes — physically implausible travel. Uses a self-join on Transactions (t1/t2), with t1.TransactionID < t2.TransactionID to avoid duplicate reversed pairs.

Current version uses a simplified "different city" rule. See sql/future_improvements.sql for a Haversine-distance-based upgrade that calculates real physical distance and implied travel speed.

3. Amount anomaly (statistical z-score)

Flags a transaction more than 3 standard deviations above that specific user's own average transaction amount — not "big" in general, but big relative to that person. Uses AVG() OVER and STDEV() OVER, partitioned per user, with NULLIF guarding against divide-by-zero for users with only one transaction.

A note on the data engineering process

This project involved real, documented engineering decisions and debugging — not just a clean, linear build. Some highlights (full detail in docs/project-log.md):

• Sampling redesign: an initial row-level random sample destroyed per-user transaction sequences, making behavioral detection impossible. Rebuilt to sample by user (keeping full per-user history) instead.
• Scientific notation parsing: PaySim's amount fields sometimes use scientific notation (e.g. 3.13E7), which DECIMAL can't parse directly. Fixed by casting through FLOAT first.
• Outlier masking: an early version of the amount-anomaly test data used too small a baseline, letting the planted outlier inflate its own comparison average enough to hide itself — a real, known statistical pitfall, fixed by using a larger, more realistic baseline.
• Synthetic test case injection: PaySim's sender IDs are largely non-repeating, so labeled synthetic test cases were deliberately planted for each detection rule to validate the logic against known, expected outcomes.

How to run this project

Run the scripts in sql/ in order:

1. 01_Database_And_Staging.sql — creates the database, staging table, and bulk-loads the CSV (update the file path first)
2. 02_Sampling.sql — builds a manageable, user-preserving sample
3. 03_Schema.sql — creates Users, Transactions, Cities
4. 04_Populate_and_Enrich.sql — populates clean tables and adds city enrichment
5. 05_Synthetic_Test_Cases.sql — plants labeled test cases for each detection rule
6. 06_Detection_Queries.sql — the three detection queries (velocity, impossible travel, amount anomaly)
7. 07_Combine_View.sql — combines all three rules into the FlaggedTransactions view

Results (on the sampled dataset)

Rule	Flagged transactions
Velocity (rapid repeat)	~403
Impossible travel	~156
Amount anomaly	~193

Future improvements

See sql/future_improvements.sql for:

• A Haversine-distance-based version of the impossible-travel check, using real coordinates instead of a flat "different city" rule
• A precision/recall scoring query, using PaySim's ground-truth IsFraud labels to quantify how well the detection rules actually perform

Tech

SQL Server (T-SQL) — window functions, CTEs, self-joins, aggregate window functions, views, BULK INSERT.