Financial database analytics portfolio demonstrating advanced SQL skills across customer segmentation, transaction analysis, fraud detection, and loan portfolio management.
customers ──┬── accounts ──── transactions
│
├── loans
│
└── accounts ──── security_events
- customers - 30 records across retail/premium/business segments
- accounts - 50 accounts (checking, savings, credit, investment)
- transactions - 200 financial transactions across 9 categories
- loans - 20 loans (personal, mortgage, auto, business)
- security_events - 50 security audit log entries
| File | Topic | SQL Features |
|---|---|---|
| 01_customer_segmentation.sql | RFM analysis, lifetime value, segments | CTEs, NTILE, window functions |
| 02_transaction_analysis.sql | Revenue trends, category breakdown, patterns | SUM OVER, ROLLUP, LAG, EXTRACT |
| 03_fraud_detection.sql | Anomaly detection, velocity checks, account takeover | Subqueries, STDDEV, window frames |
| 04_loan_portfolio.sql | Risk tiers, delinquency, interest projections | CASE, CTEs, aggregation |
| 05_executive_dashboard.sql | KPIs, moving averages, acquisition trends | Moving AVG, cumulative SUM |
| 06_advanced_analytics.sql | Chain analysis, top-N, pivoting, gap analysis | Recursive CTEs, ROW_NUMBER, PERCENT_RANK |
- Common Table Expressions (CTEs) and recursive CTEs
- Window functions: ROW_NUMBER, RANK, NTILE, LAG, LEAD, SUM OVER, AVG OVER
- Aggregation: GROUP BY, ROLLUP, HAVING
- CASE expressions and conditional aggregation
- Subqueries and correlated subqueries
- Statistical functions: STDDEV, PERCENT_RANK
- Date functions: EXTRACT, DATE_TRUNC, interval arithmetic
- Index-aware query design
mysql -u root -p < schema/create_tables.sql
mysql -u root -p < schema/seed_data.sql
mysql -u root -p < queries/01_customer_segmentation.sqlpsql -f schema/create_tables.sql
psql -f schema/seed_data.sql
psql -f queries/01_customer_segmentation.sqlTaofik Bishi - Finance & Cybersecurity @ Georgia State University
MIT License