🏦 Banking SQL Toolkit

A production-grade PostgreSQL project simulating a banking application support environment — combining a realistic financial transaction schema with an incident and SLA tracking layer on top.

Built to showcase advanced SQL skills relevant to Senior Application Support and SRE roles in financial services. Tech Stack: PostgreSQL 15 · Advanced SQL · Stored Procedures · Views · Performance Tuning

---

🎯 What This Project Covers

Topic	What's Demonstrated
Window Functions	Running totals, rank by customer spend, lag/lead for trend detection, SLA breach time calculations
CTEs & Recursive Queries	Multi-step incident analysis, account hierarchy traversal, reconciliation pipelines
Performance Tuning	Slow query identification, EXPLAIN ANALYZE, index strategy, before/after comparisons
Stored Procedures & Views	Auto-escalation logic, SLA breach detection, operational dashboard views

---

📁 Project Structure

banking-sql-toolkit/
├── schema/
│   ├── 01_create_tables.sql          # Full schema: banking + incident tracking
│   └── 02_create_indexes.sql         # Index strategy with rationale
├── data/
│   └── 03_seed_data.sql              # Realistic sample data (1000+ rows)
├── queries/
│   ├── 01_window_functions/
│   │   ├── running_balance.sql       # Running account balance over time
│   │   ├── transaction_rank.sql      # Rank transactions by value per customer
│   │   ├── lag_lead_anomaly.sql      # Detect sudden transaction spikes
│   │   └── sla_breach_time.sql       # Time between incident open and breach
│   ├── 02_ctes_recursive/
│   │   ├── incident_chain.sql        # Full incident → escalation → resolution chain
│   │   ├── account_hierarchy.sql     # Recursive account parent/child traversal
│   │   ├── daily_reconciliation.sql  # Multi-step reconciliation CTE pipeline
│   │   └── top_offenders.sql        # Customers with repeated failed transactions
│   ├── 03_performance_tuning/
│   │   ├── slow_query_before.sql     # Unoptimised query with full table scan
│   │   ├── slow_query_after.sql      # Optimised version with index + CTE
│   │   ├── explain_analysis.sql      # EXPLAIN ANALYZE walkthroughs
│   │   └── index_usage_report.sql    # Query to find missing or unused indexes
│   ├── 04_stored_procedures/
│   │   ├── sp_raise_incident.sql     # Auto-raise incident from failed transactions
│   │   ├── sp_escalate_incident.sql  # Escalate breached SLA incidents
│   │   ├── sp_resolve_incident.sql   # Resolve and log resolution details
│   │   └── sp_daily_reconciliation.sql # End-of-day reconciliation procedure
│   └── 05_views/
│       ├── vw_sla_dashboard.sql      # Live SLA breach status per incident
│       ├── vw_transaction_health.sql # Real-time transaction error rate view
│       ├── vw_queue_monitor.sql      # Message queue depth and age
│       └── vw_incident_summary.sql   # Open incidents with priority and age
├── docs/
│   ├── SCHEMA.md                     # Entity relationship description
│   └── RUNBOOK.md                    # How to use these queries in production
└── README.md

---

🚀 Quick Start

Prerequisites

• PostgreSQL 15+ (or Docker)

1. Start PostgreSQL with Docker

docker run -d \
  --name banking-sql \
  -e POSTGRES_PASSWORD=test \
  -e POSTGRES_DB=bankingdb \
  -p 5432:5432 \
  postgres:15

2. Run setup scripts in order

psql -h localhost -U postgres -d bankingdb -f schema/01_create_tables.sql
psql -h localhost -U postgres -d bankingdb -f schema/02_create_indexes.sql
psql -h localhost -U postgres -d bankingdb -f data/03_seed_data.sql

3. Run the stored procedures and views

psql -h localhost -U postgres -d bankingdb -f queries/04_stored_procedures/sp_raise_incident.sql
psql -h localhost -U postgres -d bankingdb -f queries/04_stored_procedures/sp_escalate_incident.sql
psql -h localhost -U postgres -d bankingdb -f queries/04_stored_procedures/sp_resolve_incident.sql
psql -h localhost -U postgres -d bankingdb -f queries/04_stored_procedures/sp_daily_reconciliation.sql
psql -h localhost -U postgres -d bankingdb -f queries/05_views/vw_sla_dashboard.sql
psql -h localhost -U postgres -d bankingdb -f queries/05_views/vw_transaction_health.sql
psql -h localhost -U postgres -d bankingdb -f queries/05_views/vw_queue_monitor.sql
psql -h localhost -U postgres -d bankingdb -f queries/05_views/vw_incident_summary.sql

4. Try a query

psql -h localhost -U postgres -d bankingdb -f queries/01_window_functions/running_balance.sql

---

🗺️ Roadmap

• Schema — banking + incident tracking
• Realistic seed data
• Window function queries
• CTE and recursive queries
• Performance tuning examples
• Stored procedures
• Operational views
• Partitioning strategy for transactions table
• Audit trail trigger
• Python integration: run queries and export to CSV

---

👤 Author

Manoj Kumar Purushothaman Senior Application Support Analyst | 10+ years in Banking Application Support LinkedIn · London, UK

---

📄 Licence

MIT