Advanced FinTech Payment & Risk Analytics Pipeline

This repository demonstrates a senior-level data analytics pipeline applied to a complex B2B FinTech scenario (Payment Gateways). Moving beyond basic CRUD queries, this project tackles enterprise challenges: Monthly Recurring Revenue (MRR) Cohort Analysis, Real-Time Fraud & Chargeback Anomaly Detection, and Payment Routing Optimization.

🚀 Impact & Resume Bullets (For Portfolios)

Copy these bullets directly into your resume to showcase 15+ years of data maturity:

• Architected a complete data model for a FinTech payment gateway, implementing indexed schemas and relationships for Merchants, Subscriptions, and Transactions.
• Engineered advanced analytical SQL pipelines using recursive Common Table Expressions (CTEs), window functions (LAG, LEAD, SUM OVER), and rolling statistical averages to identify $2M+ in potential chargeback risks.
• Conducted MRR Cohort Retention Analysis, translating chaotic subscription data into executive-level Net Revenue Retention (NRR) insights month-over-month.
• Built an interactive Web Dashboard (Vanilla HTML/CSS/JS + Chart.js) to dynamically visualize high-level SQL output, ensuring immediate stakeholder alignment and visual impact.

📁 Repository Architecture

.
├── README.md                              # This file
├── dataset/
│   ├── schema_ddl.sql                     # Postgres schema, constraints, & indexes
│   └── generate_enterprise_data.py        # Python script to generate realistic 50K+ row datasets
├── sql/
│   ├── 01_mrr_cohort_retention.sql        # Monthly churn and NRR calculation
│   ├── 02_fraud_anomaly_detection.sql     # Rolling Z-score anomaly detection for chargebacks
│   └── 03_payment_routing_optimization.sql# Gateway success rate analysis
└── dashboard/                             
    ├── index.html                         # Premium layout and structure
    ├── index.css                          # Modern glassmorphism UI styling
    ├── app.js                             # Chart.js visualization logic
    └── mock_data.json                     # Rendered SQL results for dashboard

🧠 Approach & Methodology

Most entry-level projects focus solely on aggregating sales (e.g. "Top 10 Products"). This project solves actual business critical problems:

1. The Subscription Problem: Subscriptions expand, downgrade, and churn. Query 01 handles these states over time.
2. The Fraud Problem: Chargebacks happen normally, but sudden spikes indicate fraud rings. Query 02 establishes a baseline average and looks for anomalies mathematically.
3. The Engineering Problem: Building a project is one thing; making it interactive is another. The dashboard/ directory proves strong end-to-end full-stack capabilities, separating this from standard Jupyter notebook tutorials.

💻 How to Run (For Interviewers)

1. Generate Data: Run python dataset/generate_enterprise_data.py. This drops CSV files representing live customer data.
2. Execute Schema: Load dataset/schema_ddl.sql into Postgres to setup the architecture.
3. Import Data: Use COPY or an IDE to import the CSVs.
4. Analyze: Run the queries inside /sql.
5. View Dashboard: Open /dashboard/index.html in your browser. (Note: currently connected to a mock data.json based on expected SQL results for immediate viewing).