🏦 Bank Transactions SQL Analysis

A real-world inspired SQL case study showcasing my ability to analyze financial data, detect fraud, and generate business insights — directly relevant to Data Analyst roles.

🔹 Designed & implemented a banking database (8 tables, 80K+ records)
🔹 Solved 25+ advanced SQL problems (ETL, reporting, fraud detection, optimization)
🔹 Delivered business insights on customer segmentation, fraud %, branch performance, and channel usage

---

🎯 Project Objectives

• Design a normalized relational database simulating a bank’s core operations.
• Import and clean large datasets (80K+ rows) into PostgreSQL.
• Solve 25+ advanced SQL problems covering:
• Customer segmentation & high-value client analysis
• Transaction trend reporting (volume, value, growth)
• Fraud detection & anomaly tracking
• Branch & employee performance evaluation
• ATM vs. Online vs. Cash channel usage
• Apply advanced SQL concepts: joins, window functions, CTEs, indexing, optimization.

---

🏗️ Database Design

• Core Tables: Customers, Accounts, Transactions, Cards, Employees, Branches, ATMs, ATM_Transactions
• ERD:
  

---

📂 Project Structure

├── create_table_query.sql    # Database schema
├── problems.txt              # 25 advanced business questions
├── solutions.txt             # SQL solutions + explanations
├── Bank_data.zip             # Contains 8 CSV datasets (80K+ records)
├── images/
│ ├── ER_Diagram.png          # Schema diagram
│ └── output.png              # Query + output example
└── README.md                 # Project documentation

---

📊 Example Business Problems Solved

Some of the 25 SQL challenges I solved:

1. Top 10 customers by balance → Identify high-value clients.
2. Monthly transaction volume & value → Track business growth trends.
3. Fraud detection per branch → Branch-wise anomaly detection.
4. Channel usage breakdown (ATM vs Online vs Cash) → Customer preference analysis.
5. Employee performance via managed accounts → Data-driven HR insights.

👉 Full problems list → problems.txt
👉 SQL solutions + explanations → solutions.txt

---

📝 Query Showcase (Example)

Problem: Detect customers who withdraw more than they deposit in the last year.

SELECT c.customer_id, c.first_name, c.last_name,
    SUM(CASE WHEN t.transaction_type='Deposit' THEN t.amount ELSE 0 END) AS total_deposit,
    SUM(CASE WHEN t.transaction_type='Withdrawal' THEN t.amount ELSE 0 END) AS total_withdrawal
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id
JOIN transactions t ON t.account_id = a.account_id
WHERE t.transaction_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY c.customer_id
HAVING SUM(CASE WHEN t.transaction_type='Withdrawal' THEN t.amount ELSE 0 END) >
       SUM(CASE WHEN t.transaction_type='Deposit' THEN t.amount ELSE 0 END);

Output :

---

⚙️ How to Run

1. Create a new PostgreSQL database:

   CREATE DATABASE bank_db;

2. Run schema file to create tables:

   psql -d bank_db -f create_table_query.sql

3. Import CSVs from Bank_data.zip into respective tables.
4. Run queries from solutions.txt to reproduce analysis.

---

🛠️ Tools & Technologies

• SQL (PostgreSQL) → Core analysis & queries
• CSV Data Import → Simulated real-world datasets
• ERD Tools → Schema design (dbdiagram.io / draw.io)

---

🚀 Key Skills Demonstrated

✔️ ETL & Data Cleaning – Imported & cleaned 80K+ records from CSVs ✔️ Database Design – Built normalized schema with 8 interrelated tables ✔️ Advanced SQL Queries – Joins, aggregations, window functions, CTEs, indexes ✔️ Fraud & Risk Analysis – Detected anomalies and calculated fraud % per branch ✔️ Business Reporting – Generated trend reports & performance insights

---

📊 Project Impact

• Detected fraudulent transactions with clear branch-wise percentages.
• Identified top customers and profitable branches for better business strategy.
• Highlighted customer channel preferences (ATM vs Online vs Cash).
• Built SQL reports that can directly support decision-making for banking operations.

---

📈 Project Relevance for Data Analyst Roles

This project mirrors real-world financial data analysis tasks such as:

• Understanding customer behavior
• Detecting fraudulent activities
• Monitoring employee & branch performance
• Preparing data-driven reports

It demonstrates my ability to turn raw banking data into actionable insights using SQL — a key skill for Data Analyst and Business Intelligence roles.

---

📧 Contact

🔗 LinkedIn – Harsh Belekar