💳 WavePay Fintech — Internal Analytics Platform

Pure PostgreSQL Portfolio Project

Author: Arogundade Caleb Oluwadamilola | TCG Analytics Engine: PostgreSQL 14+ | Domain: Fintech · Digital Payments · Fraud Intelligence

---

Overview

A production-grade SQL project that simulates the internal analytics platform of a Nigerian fintech company at Flutterwave / Paystack scale — processing digital payments across Nigeria, Ghana, Kenya and 12 global remittance corridors.

No Python. No Pandas. Pure SQL.

Every line of analysis — from fraud detection to CBN compliance to settlement SLA tracking — is written in PostgreSQL. This is the kind of SQL a Senior Data Analyst at a fintech company actually writes on the job.

---

Project Structure

File	Contents	Lines
01_schema.sql	9 tables, 2 views, indexes, column comments	~360
02_seed_data.sql	50 customers, 20 merchants, 80+ transactions, remittances, fraud flags, settlements	~340
03_analytics.sql	12 business-critical analytical queries	~580
04_advanced_features.sql	Materialized views, functions, procedures, triggers, bonus queries	~580

Total: 1,800+ lines of production-quality PostgreSQL

---

Database Schema

countries          → 15 operating markets with FX rates
banks              → 15 partner banks across Africa, UK, US
customers          → 50 wallet holders (Nigeria, diaspora, Ghana, Kenya)
merchants          → 20 businesses (e-commerce, transport, telco, govt)
wallets            → 25 wallet ledgers with balance and lifecycle state
transactions       → 80 payment events (all types, channels, statuses)
remittances        → 25 cross-border transfers across 12 corridors
fraud_flags        → 10 fraud/AML flags with rule triggers and risk scores
settlement_ledger  → 20 merchant settlement batches
audit_log          → Auto-populated by triggers

---

The 12 Analytical Queries (03_analytics.sql)

Query	Business Question	Key Techniques
Q01	Monthly volume & revenue trend	LAG(), SUM() OVER, FILTER, DATE_TRUNC
Q02	Channel performance deep-dive	RANK(), conditional aggregation, market share
Q03	Merchant Pareto — 20%/80% rule	NTILE(), cumulative %, revenue concentration
Q04	Customer RFM segmentation	NTILE() × 3, composite scoring, CASE tiering
Q05	Wallet lifecycle cohort	CASE date buckets, utilisation rate, JOIN × 3
Q06	Remittance corridor intelligence	RANK() × 3, fee %, speed, compliance risk
Q07	Fraud pattern profiling	EXTRACT, JOIN × 3, time/amount bucketing
Q08	Failed transaction forensics	Revenue leakage, RANK() by channel and global
Q09	CBN regulatory compliance	STR/CTR thresholds, KYC risk, compliance actions
Q10	Settlement SLA breach analysis	CASE SLA logic, pending float, breach rate
Q11	Customer lifetime value ranking	RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, LEAD, LAG
Q12	Executive KPI dashboard	CROSS JOIN multi-CTE, full platform health

---

Advanced Features (04_advanced_features.sql)

Materialized Views (3)

• mv_merchant_leaderboard — Pre-aggregated merchant rankings (daily refresh)
• mv_daily_platform_summary — Daily volume by channel/type (powers BI dashboards)
• mv_corridor_summary — Remittance corridor intelligence

Scalar Functions (5)

• fn_calculate_fee() — WavePay fee engine (mirrors Flutterwave/Paystack pricing)
• fn_wallet_lifecycle() — Lifecycle stage from timestamp
• fn_cbn_flag() — CBN regulatory classification (STR/CTR)
• fn_ngn_to_usd() — FX conversion utility
• fn_rfm_label() — RFM segment label from composite score

Table-Valued Functions (3)

• fn_wallet_statement() — Pageable statement with running balance
• fn_merchant_report() — Weekly merchant performance with WoW growth
• fn_fraud_investigation() — Analyst fraud queue by risk tier

Stored Procedures (3)

• sp_process_settlement() — Full merchant settlement workflow (MDR + VAT)
• sp_review_fraud_flag() — Analyst review → wallet freeze on confirmed fraud
• sp_refresh_all_mvs() — Refresh all materialized views

Triggers (4)

• trg_update_wallet_on_txn — Auto-updates wallet balance on completion
• trg_auto_fraud_flag — Auto-inserts fraud flag on rule match (AML/Geo/Velocity)
• trg_audit_customer — Full audit trail (INSERT/UPDATE/DELETE → JSONB log)
• trg_prevent_negative_balance — Blocks any update that overdrafts a wallet

Bonus Window Function Queries (4)

• Velocity detection — LEAD() gap analysis for rapid successive transactions
• Cohort retention — Month-over-month wallet retention rate
• Amount percentile distribution — P25/P50/P75/P90/P95/P99 by channel
• Gap analysis — REGR_SLOPE() for engagement trend (widening vs narrowing gaps)

---

How to Run

-- Step 1: Create the database
CREATE DATABASE wavepay;
\c wavepay

-- Step 2: Run in order
\i 01_schema.sql
\i 02_seed_data.sql
\i 03_analytics.sql
\i 04_advanced_features.sql

-- Or from terminal:
psql -U postgres -d wavepay -f 01_schema.sql
psql -U postgres -d wavepay -f 02_seed_data.sql
psql -U postgres -d wavepay -f 03_analytics.sql
psql -U postgres -d wavepay -f 04_advanced_features.sql

---

SQL Concepts Demonstrated

Concept	Queries
Window Functions: RANK, DENSE_RANK, ROW_NUMBER	Q02, Q03, Q06, Q11
Window Functions: LAG, LEAD	Q01, Q07, Q11, Bonus 1, 4
Window Functions: PERCENT_RANK, CUME_DIST	Q11
Window Functions: NTILE (quartile/quintile)	Q03, Q04
Window Functions: SUM() OVER (running totals)	Q01, Q11
Ordered Set Aggregates: PERCENTILE_CONT	Bonus 3
Regression: REGR_SLOPE	Bonus 4
CTEs (Common Table Expressions) — chained	All queries
Conditional aggregation (FILTER)	Q01–Q12
CROSS JOIN for multi-domain summary	Q12
Materialized views with CONCURRENTLY refresh	Section 1
Stored procedures with OUT parameters	SP 1
Triggers with NEW/OLD row access	Section 5
Generated columns (STORED)	Schema
JSONB audit logging	Trigger 3
Partial indexes (WHERE clause)	Section 6
Date arithmetic (INTERVAL, EXTRACT, EPOCH)	Multiple

---

Business Context

WavePay (fictional) is modelled on the real operating patterns of:

• Flutterwave — multi-currency API payments across Africa
• Paystack (Stripe-backed) — merchant checkout and settlements
• OPay / Moniepoint — mobile wallet and agent banking

The analytics queries answer questions that real fintech analysts at these companies handle daily — from regulatory reporting to fraud detection to merchant settlement disputes.

---

Key Findings from the Data

1. API and Bank Transfer channels process the highest-value transactions but contribute a disproportionate share of fraud flags.

2. UK→Nigeria GBP corridor is the single highest-volume remittance lane — a dedicated diaspora product could unlock significant revenue.

3. CBN compliance gaps: Wallets with KYC Level 1 are executing transactions above the ₦5M STR threshold — an immediate regulatory risk.

4. Settlement float: Enterprise merchants have the tightest SLA (T+1) but also the largest pending float — any delay has the highest financial impact.

5. Night-time large transfers (00:00–04:00, ≥₦200K) are the most consistent fraud signal — a dedicated velocity rule reduces false positives.

---

About the Author

Arogundade Caleb Oluwadamilola is an MBBS student, Data Analyst, WHO Student Liaison Officer, and Founder of TCG Analytics — with a professional focus at the intersection of clinical intelligence and financial data analytics.

"Turning Data Into Decisions. Intelligence Into Impact."

---

All data is synthetic and generated for analytical demonstration purposes only.