📚 100 DAYS SQL

CODING CHALLENGE

DAY 13

Master SQL One Problem at a Time

✨ Daily SQL Problems
✨ Real-World Scenarios
✨ Complete Solutions
✨ Interview Ready

💳 Banking Fraud Detection System

Database Schema - RAW DATA (Messy!)

Table 1: transactions - Sample data (25 rows total)

txn_id	customer_id	merchant_id	amount	txn_date	status
TXN001	C12345	M001	$2,500.00	2024-01-15 14:23:11	COMPLETED
TXN002	c67890	m002	45.5	15/01/2024	pending
TXN003	C12345	M003	$-150	2024-01-15 14:25:00	Refund
TXN004	C99999	M001	15000.00	2024/01/16 03:45	FLAGGED

...and 21 more messy rows! Download the full dataset above.

Table 2: customers - Customer accounts (20 rows)

customer_id	full_name	email	account_status
C12345	john smith	JOHN.SMITH@EMAIL.COM	active
c67890	SARAH JONES	sarah@invalid	ACTIVE
C99999	anna garcia	anna@email.co	FLAGGED

Table 3: merchants - Merchant risk levels

merchant_id	merchant_name	category	risk_level
M001	Amazon Store	Retail	LOW
M004	Crypto Exchange Pro	Finance	HIGH
M005	Online Casino 24/7	Gambling	HIGH

Table 4: fraud_reports - Reported fraud cases

report_id	txn_id	fraud_type	verified
FR001	TXN004	Suspicious Amount	Yes
FR003	TXN015	Multiple Attempts	Yes

📋 Problem Requirements

Build a fraud detection pipeline to identify suspicious transactions, validate data quality, and generate risk scores for banking security.

🧹 Data Cleaning Tasks:

Standardize customer_id & merchant_id: uppercase, trim spaces
Clean amount: remove $, commas, handle NULL/negative values
Standardize dates: convert all to YYYY-MM-DD HH:MM:SS format
Standardize status: lowercase, consistent naming
Clean emails: trim spaces, lowercase, validate format with REGEXP
Standardize country: uppercase, remove extra spaces

🔗 JOIN & Enrichment:

JOIN with customers to get account details and status
JOIN with merchants to get risk levels and categories
LEFT JOIN with fraud_reports to check if already reported
Flag orphan transactions (no customer/merchant match)
Identify transactions with HIGH risk merchants

⚠️ Fraud Detection Rules:

Flag transactions with amount ≥ $10,000 as "High Amount"
Flag transactions with HIGH risk merchant (Crypto, Gambling)
Use LAG() to find time between consecutive transactions by customer
Flag rapid transactions (< 5 minutes apart) as suspicious
Flag transactions from suspended/flagged customer accounts
Calculate fraud risk score: 0-100 (sum of all risk factors)
Categorize as: Low Risk (0-30), Medium (31-60), High (61-100)

💡 SQL Skills Required: TRIM, UPPER, LOWER, REPLACE, CASE, CAST, STR_TO_DATE, REGEXP, Window Functions (LAG, TIMESTAMPDIFF), JOINs, CTEs, COALESCE

This mirrors real-world banking fraud detection systems used by financial institutions daily!

🎉 Day 13 Complete!

Congratulations!

You've learned banking fraud detection using window functions, complex JOINs, and multi-factor risk scoring! This is exactly what financial data engineers build in production systems - fraud detection pipelines that protect billions in assets.

🔗 Connect & Follow

Join me on the 100 Days SQL journey!

LinkedIn: linkedin.com/in/rahulkanche
GitHub: github.com/rahulkanche/100-days-sql-data

💪 Keep Practicing!

✓ Master fraud detection patterns
✓ Build risk scoring systems
✓ Use window functions for time analysis
✓ Prepare for Banking/FinTech interviews

Follow the 100 Days SQL Challenge
Day 14 tomorrow - Master SQL one problem at a time!

Rahul Kanche — exploring data, decoding clarity!