mirror of
https://github.com/freedbygrace/SQL.git
synced 2026-07-26 11:28:16 +00:00
Alphaeus Mote
b30733ccad
- Docker setup with PostgreSQL 16 and DB-UI web interface - Comprehensive 20+ table schema with fraud detection patterns - Idempotent shell scripts for data generation (no Python dependency) - Realistic geographic data (100 US cities, 210 world cities) - 5M+ transactions with embedded fraud patterns (velocity, geographic, structuring, etc.) - Progressive SQL exercises from beginner to advanced fraud detection - Complete documentation and quick start guide - Setup and verification scripts
207 lines
3.9 KiB
Markdown
207 lines
3.9 KiB
Markdown
# Level 1: Basic SQL Queries
|
|
|
|
## Introduction
|
|
Welcome to the Financial Fraud Detection SQL learning path! In this first level, you'll learn the fundamentals of SQL by querying a realistic fraud detection database.
|
|
|
|
## Learning Objectives
|
|
- Understand SELECT statements
|
|
- Use WHERE clauses for filtering
|
|
- Sort results with ORDER BY
|
|
- Limit result sets
|
|
- Work with basic comparison operators
|
|
|
|
## Exercises
|
|
|
|
### Exercise 1.1: View All Customers
|
|
**Objective:** Retrieve all customer records
|
|
|
|
```sql
|
|
-- Your query here
|
|
SELECT * FROM customers;
|
|
```
|
|
|
|
**Expected Result:** All customer records with all columns
|
|
|
|
---
|
|
|
|
### Exercise 1.2: Find a Specific Customer
|
|
**Objective:** Find customer with customer_id = 1
|
|
|
|
```sql
|
|
-- Your query here
|
|
SELECT * FROM customers WHERE customer_id = 1;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.3: High-Risk Customers
|
|
**Objective:** Find all customers with a risk_score greater than 80
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Hint:** Use the WHERE clause with the > operator
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT customer_id, first_name, last_name, email, risk_score
|
|
FROM customers
|
|
WHERE risk_score > 80
|
|
ORDER BY risk_score DESC;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.4: Recent Registrations
|
|
**Objective:** Find customers who registered in 2024
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Hint:** Use WHERE with date comparison
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT customer_id, first_name, last_name, email, registration_date
|
|
FROM customers
|
|
WHERE registration_date >= '2024-01-01'
|
|
ORDER BY registration_date DESC;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.5: Inactive Accounts
|
|
**Objective:** Find all inactive customer accounts
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT customer_id, first_name, last_name, email, is_active
|
|
FROM customers
|
|
WHERE is_active = FALSE;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.6: Top 10 Largest Transactions
|
|
**Objective:** Find the 10 largest transactions by amount
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Hint:** Use ORDER BY with LIMIT
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT transaction_id, account_id, amount, transaction_date, description
|
|
FROM transactions
|
|
ORDER BY amount DESC
|
|
LIMIT 10;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.7: Flagged Transactions
|
|
**Objective:** Find all transactions that have been flagged for review
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT transaction_id, account_id, amount, fraud_score, flagged_reason
|
|
FROM transactions
|
|
WHERE is_flagged = TRUE
|
|
ORDER BY fraud_score DESC;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.8: International Transactions
|
|
**Objective:** Find all international transactions over $1,000
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT transaction_id, account_id, amount, country_id, city
|
|
FROM transactions
|
|
WHERE is_international = TRUE AND amount > 1000
|
|
ORDER BY amount DESC;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.9: Specific Merchant Categories
|
|
**Objective:** Find all merchants in the 'Gambling' or 'Cryptocurrency' categories
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Hint:** Join merchants with merchant_categories, use IN or OR
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT m.merchant_id, m.merchant_name, mc.category_name, m.risk_rating
|
|
FROM merchants m
|
|
JOIN merchant_categories mc ON m.category_id = mc.category_id
|
|
WHERE mc.category_name IN ('Gambling', 'Cryptocurrency')
|
|
ORDER BY m.risk_rating DESC;
|
|
```
|
|
|
|
---
|
|
|
|
### Exercise 1.10: Critical Alerts
|
|
**Objective:** Find all open alerts with CRITICAL severity
|
|
|
|
```sql
|
|
-- Your query here
|
|
|
|
```
|
|
|
|
**Solution:**
|
|
```sql
|
|
SELECT alert_id, customer_id, alert_type, description, alert_date
|
|
FROM alerts
|
|
WHERE severity = 'CRITICAL' AND status = 'OPEN'
|
|
ORDER BY alert_date DESC;
|
|
```
|
|
|
|
---
|
|
|
|
## Challenge Exercises
|
|
|
|
### Challenge 1.1: PEP Customers
|
|
Find all Politically Exposed Persons (PEPs) with high risk scores (> 70)
|
|
|
|
### Challenge 1.2: Expired Cards
|
|
Find all cards that have expired (expiry_date < current_date)
|
|
|
|
### Challenge 1.3: Large Cash Advances
|
|
Find all cash advance transactions over $5,000
|
|
|
|
---
|
|
|
|
## Next Steps
|
|
Once you're comfortable with these basic queries, move on to:
|
|
- **Level 2:** JOIN operations
|
|
- **Level 3:** Aggregate functions and GROUP BY
|
|
|