🚀 SQL Project Series #10

Finance & Expense Tracker Analysis 💰

Build a real-world finance analytics project to track income, expenses, savings, budgets, and cash flow using SQL.

🎯 Business Objectives

✅ Track monthly income and expenses

✅ Analyze spending by category

✅ Monitor savings trends

✅ Compare budget vs actual spending

✅ Identify high-expense categories

✅ Analyze cash flow

✅ Track recurring expenses

✅ Generate financial dashboards

📂 Step 1: Create Database

CREATE DATABASE finance_db;
USE finance_db;

📂 Step 2: Create Categories Table

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50),
    transaction_type VARCHAR(20)
);

📂 Step 3: Create Accounts Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(50),
    account_type VARCHAR(30),
    opening_balance DECIMAL(12,2)
);

📂 Step 4: Create Transactions Table

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    account_id INT,
    category_id INT,
    transaction_date DATE,
    amount DECIMAL(12,2),
    description VARCHAR(255),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

📂 Step 5: Insert Sample Categories

INSERT INTO categories VALUES
(1,'Salary','Income'),
(2,'Freelancing','Income'),
(3,'Rent','Expense'),
(4,'Groceries','Expense'),
(5,'Utilities','Expense'),
(6,'Entertainment','Expense'),
(7,'Transport','Expense');

📂 Step 6: Insert Sample Accounts

INSERT INTO accounts VALUES
(101,'Savings Account','Bank',50000),
(102,'Credit Card','Card',0),
(103,'Cash Wallet','Cash',5000);

📂 Step 7: Insert Sample Transactions

INSERT INTO transactions VALUES
(1001,101,1,'2025-01-01',85000,'Monthly Salary'),
(1002,101,3,'2025-01-03',18000,'House Rent'),
(1003,101,4,'2025-01-05',4200,'Supermarket'),
(1004,102,6,'2025-01-08',2500,'Movie & Dinner'),
(1005,103,7,'2025-01-09',800,'Cab Fare'),
(1006,101,5,'2025-01-12',2200,'Electricity Bill'),
(1007,101,2,'2025-01-18',15000,'Freelance Project');

🧠 SQL Concepts You'll Practice

✔ DDL & DML

✔ Joins

✔ Aggregate Functions

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ CTEs

✔ Window Functions

✔ Date Functions

✔ Financial Calculations

📊 Business KPIs You Can Build

📈 Total Income

📈 Total Expenses

📈 Net Savings

📈 Savings Rate

📈 Monthly Cash Flow

📈 Income by Source

📈 Expenses by Category

📈 Highest Expense Category

📈 Budget vs Actual Spending

📈 Average Daily Spending

📈 Monthly Spending Trend

📈 Running Account Balance

📈 Recurring Expense Analysis

📈 Weekend vs Weekday Spending

📈 Top 10 Largest Transactions

📈 Account-wise Balance

📈 Income Growth Rate

📈 Expense Growth Rate

📈 Category-wise Contribution

📈 Financial Health Dashboard

🎯 This project reflects real-world SQL work performed by Financial Analysts, FP&A teams, FinTech companies, banks, and Business Intelligence professionals to monitor financial performance and support better decision-making.

💡 Double Tap ❤️ For More