🚀 SQL Project Series #27

Stock Market Analytics 📈

Analyze stocks, investors, portfolios, trades, and market performance using SQL to gain insights into trading behavior, portfolio performance, and investment trends.

🎯 Business Objectives
✅ Analyze stock trading activity
✅ Track investor portfolios
✅ Monitor stock price movements
✅ Measure portfolio performance
✅ Identify top-performing stocks
✅ Analyze trading volume
✅ Calculate investment returns
✅ Build executive investment dashboards

📂 Step 1: Create Database
CREATE DATABASE stock_market_db;
USE stock_market_db;

📂 Step 2: Create Investors Table
CREATE TABLE investors (
investor_id INT PRIMARY KEY,
investor_name VARCHAR(100),
city VARCHAR(50),
account_open_date DATE
);

📂 Step 3: Create Stocks Table
CREATE TABLE stocks (
stock_id INT PRIMARY KEY,
stock_symbol VARCHAR(20),
company_name VARCHAR(100),
sector VARCHAR(50)
);

📂 Step 4: Create Trades Table
CREATE TABLE trades (
trade_id INT PRIMARY KEY,
investor_id INT,
stock_id INT,
trade_date DATE,
transaction_type VARCHAR(10),
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (investor_id) REFERENCES investors(investor_id),
FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);

📂 Step 5: Create Daily Prices Table
CREATE TABLE daily_prices (
price_id INT PRIMARY KEY,
stock_id INT,
price_date DATE,
open_price DECIMAL(10,2),
close_price DECIMAL(10,2),
high_price DECIMAL(10,2),
low_price DECIMAL(10,2),
volume BIGINT,
FOREIGN KEY (stock_id) REFERENCES stocks(stock_id)
);

📂 Step 6: Insert Sample Investors
INSERT INTO investors VALUES
(1,'Rahul Sharma','Mumbai','2023-01-15'),
(2,'Priya Verma','Delhi','2023-04-20'),
(3,'Amit Patel','Pune','2024-02-10'),
(4,'Sneha Joshi','Bangalore','2024-05-05'),
(5,'Rohan Gupta','Hyderabad','2024-07-18');

📂 Step 7: Insert Sample Stocks
INSERT INTO stocks VALUES
(101,'TCS','Tata Consultancy Services','IT'),
(102,'INFY','Infosys','IT'),
(103,'RELIANCE','Reliance Industries','Energy'),
(104,'HDFCBANK','HDFC Bank','Banking'),
(105,'TATAMOTORS','Tata Motors','Automobile');

📂 Step 8: Insert Sample Trades
INSERT INTO trades VALUES
(1001,1,101,'2025-01-05','BUY',10,4200),
(1002,2,102,'2025-01-06','BUY',20,1800),
(1003,3,103,'2025-01-07','SELL',5,2900),
(1004,4,104,'2025-01-08','BUY',15,1650),
(1005,5,105,'2025-01-09','BUY',25,980);

📂 Step 9: Insert Sample Daily Prices
INSERT INTO daily_prices VALUES
(1,101,'2025-01-05',4180,4215,4235,4170,1200000),
(2,102,'2025-01-06',1785,1810,1822,1778,980000),
(3,103,'2025-01-07',2880,2910,2925,2870,2100000),
(4,104,'2025-01-08',1640,1665,1672,1635,1750000),
(5,105,'2025-01-09',970,990,995,965,3100000);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Investors
📈 Total Trades
📈 Buy vs Sell Transactions
📈 Total Trading Volume
📈 Portfolio Value
📈 Profit & Loss (P&L)
📈 Return on Investment (ROI)
📈 Average Buy Price
📈 Average Sell Price
📈 Top Performing Stocks
📈 Worst Performing Stocks
📈 Daily Price Change
📈 Monthly Trading Volume
📈 Sector-wise Performance
📈 Most Traded Stocks
📈 Investor Portfolio Diversification
📈 Average Holding Size
📈 Stock Volatility
📈 Market Capitalization Trend
📈 Executive Investment Dashboard

🎯 Double Tap ❤️ For More