🚀 SQL Project Series #36

Banking Customer & Transaction Analytics 🏦

Analyze customers, accounts, transactions, branches, and loan activity using SQL to understand customer behavior, transaction trends, account profitability, and banking operations.

🎯 Business Objectives

✅ Analyze customer activity
✅ Monitor account balances
✅ Track deposits and withdrawals
✅ Identify high-value customers
✅ Analyze branch performance
✅ Detect unusual transaction patterns
✅ Measure loan performance
✅ Build banking dashboards

📂 Step 1: Create Database

CREATE DATABASE banking_analytics_db;
USE banking_analytics_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    customer_segment VARCHAR(30),
    signup_date DATE
);

📂 Step 3: Create Branches Table

CREATE TABLE branches (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(100),
    city VARCHAR(50)
);

📂 Step 4: Create Accounts Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    customer_id INT,
    branch_id INT,
    account_type VARCHAR(30),
    opening_date DATE,
    current_balance DECIMAL(15,2),
    account_status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
);

📂 Step 5: Create Transactions Table

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    account_id INT,
    transaction_date DATETIME,
    transaction_type VARCHAR(30),
    amount DECIMAL(15,2),
    transaction_status VARCHAR(20),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

📂 Step 6: Create Loans Table

CREATE TABLE loans (
    loan_id INT PRIMARY KEY,
    customer_id INT,
    loan_type VARCHAR(50),
    loan_amount DECIMAL(15,2),
    interest_rate DECIMAL(5,2),
    loan_status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

📂 Step 7: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai','Premium','2022-01-10'),
(2,'Priya Verma','Delhi','Mass Affluent','2022-04-15'),
(3,'Amit Patel','Pune','Premium','2023-02-20'),
(4,'Sneha Joshi','Bangalore','Mass Market','2023-06-05'),
(5,'Rohan Gupta','Hyderabad','Premium','2024-01-18');

📂 Step 8: Insert Sample Branches

INSERT INTO branches VALUES
(101,'Mumbai Central','Mumbai'),
(102,'Connaught Place','Delhi'),
(103,'Pune Central','Pune'),
(104,'Bangalore Main','Bangalore');

📂 Step 9: Insert Sample Accounts

INSERT INTO accounts VALUES
(1001,1,101,'Savings','2022-01-10',250000,'Active'),
(1002,2,102,'Current','2022-04-15',520000,'Active'),
(1003,3,103,'Savings','2023-02-20',175000,'Active'),
(1004,4,104,'Savings','2023-06-05',85000,'Active'),
(1005,5,101,'Current','2024-01-18',750000,'Active');

📂 Step 10: Insert Sample Transactions

INSERT INTO transactions VALUES
(5001,1001,'2025-01-05 10:15:00','Deposit',50000,'Success'),
(5002,1001,'2025-01-07 14:30:00','Withdrawal',15000,'Success'),
(5003,1002,'2025-01-08 11:20:00','Deposit',120000,'Success'),
(5004,1003,'2025-01-10 09:45:00','Withdrawal',25000,'Success'),
(5005,1004,'2025-01-12 16:10:00','Deposit',30000,'Success'),
(5006,1005,'2025-01-15 13:25:00','Withdrawal',85000,'Success'),
(5007,1003,'2025-01-18 18:40:00','Transfer',45000,'Success');

📂 Step 11: Insert Sample Loans

INSERT INTO loans VALUES
(9001,1,'Home Loan',5000000,8.25,'Active'),
(9002,2,'Personal Loan',800000,11.50,'Active'),
(9003,3,'Car Loan',1200000,9.10,'Active'),
(9004,4,'Personal Loan',500000,12.00,'Closed'),
(9005,5,'Business Loan',3000000,10.25,'Active');

🧠 SQL Concepts You'll Practice

✔ INNER JOIN
✔ LEFT JOIN
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ CTEs
✔ Subqueries
✔ Window Functions
✔ RANK()
✔ DENSE_RANK()
✔ LAG()
✔ Date Functions
✔ Conditional Aggregation
✔ Financial KPI Calculations

📊 Business KPIs You Can Build