🚀 SQL Project Series #6: Banking Transaction Analysis 🏦

Learn how banks use SQL to analyze customer transactions, monitor account activity, detect fraud, and generate business insights.

🎯 Business Objectives

✅ Analyze customer transactions

✅ Calculate account balances

✅ Identify high-value customers

✅ Detect suspicious transactions

✅ Monitor transaction trends

✅ Analyze deposits vs withdrawals

✅ Measure customer activity

✅ Identify dormant accounts

✅ Generate banking KPIs

📂 Step 1: Create Database

CREATE DATABASE banking_db;

USE banking_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100),

gender VARCHAR(10),

city VARCHAR(50),

account_open_date DATE

);

📂 Step 3: Create Accounts Table

CREATE TABLE accounts (

account_id INT PRIMARY KEY,

customer_id INT,

account_type VARCHAR(30),

branch_name VARCHAR(100),

opening_balance DECIMAL(12,2),

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

);

📂 Step 4: Create Transactions Table

CREATE TABLE transactions (

transaction_id INT PRIMARY KEY,

account_id INT,

transaction_date TIMESTAMP,

transaction_type VARCHAR(20),

amount DECIMAL(12,2),

FOREIGN KEY (account_id)

REFERENCES accounts(account_id)

);

📂 Step 5: Insert Sample Customers

INSERT INTO customers VALUES

(1,'Rahul Sharma','Male','Mumbai','2023-01-10'),

(2,'Priya Verma','Female','Delhi','2023-02-18'),

(3,'Amit Patel','Male','Pune','2023-04-12'),

(4,'Sneha Joshi','Female','Bangalore','2023-05-22'),

(5,'Rohan Gupta','Male','Hyderabad','2023-08-01');

📂 Step 6: Insert Sample Accounts

INSERT INTO accounts VALUES

(101,1,'Savings','Mumbai',50000),

(102,2,'Savings','Delhi',35000),

(103,3,'Current','Pune',100000),

(104,4,'Savings','Bangalore',45000),

(105,5,'Current','Hyderabad',75000);

📂 Step 7: Insert Sample Transactions

INSERT INTO transactions VALUES

(1001,101,'2025-01-05 10:15:00','Deposit',15000),

(1002,101,'2025-01-06 12:30:00','Withdrawal',5000),

(1003,102,'2025-01-07 14:20:00','Deposit',25000),

(1004,103,'2025-01-08 16:10:00','Withdrawal',30000),

(1005,104,'2025-01-09 09:45:00','Deposit',12000),

(1006,105,'2025-01-10 11:00:00','Withdrawal',10000);

🧠 SQL Concepts You'll Practice

✔ DDL Commands

✔ DML Commands

✔ Primary & Foreign Keys

✔ Joins

✔ Aggregate Functions

✔ CASE WHEN

✔ GROUP BY

✔ HAVING

✔ Window Functions

✔ CTEs

✔ Date & Time Functions

📊 Business KPIs You Can Build

📈 Total Transaction Amount

📈 Total Deposits

📈 Total Withdrawals

📈 Net Cash Flow

📈 Current Account Balance

📈 Average Transaction Amount

📈 Daily Transaction Volume

📈 Monthly Transaction Trend

📈 Branch-wise Transactions

📈 Account Type Analysis

📈 Top Customers by Transaction Value

📈 Most Active Customers

📈 Dormant Accounts

📈 High-Value Transactions

📈 Deposit vs Withdrawal Ratio

📈 Fraud Detection Alerts

📈 Average Balance by Branch

📈 Customer Growth

📈 Transaction Success Rate

📈 Peak Transaction Hours

💡 Double Tap ❤️ For More