Ecommerce-SQL-Analysis/README.md at main · DeveshShukla23/Ecommerce-SQL-Analysis

🛒 About This Project

project = {
    "name"      : "E-Commerce SQL Analysis",
    "database"  : "MySQL — Star Schema Design",
    "queries"   : 7,
    "concepts"  : ["JOINs", "CTEs", "Window Functions", "CASE WHEN",
                   "GROUP BY", "Aggregate Functions", "Date Functions"],
    "data"      : "100% Synthetic — generated using SQL itself",
    "tables"    : ["customers", "products", "orders", "order_items"],
    "records"   : "1,000 customers | 5,000 orders | 8,000 order items",
    "outcome"   : "📊 7 CEO-level business insights extracted from raw data"
}

💡 "I built an entire E-Commerce business from scratch — just to ask it 7 questions."

📊 Dataset — Self Created Synthetic Data

📦 Products	👥 Customers	🛒 Orders	🧾 Order Items
10 Products	1,000 Customers	5,000 Orders	8,000+ Line Items

⚡ All data was synthetically generated using SQL itself — no external CSV or dataset used!

🗄️ Database Schema

customers ──< orders ──< order_items >── products

Table	Description	Key Columns
`customers`	Customer information	customer_id, name, email, country
`products`	Product catalog	product_id, name, category, price
`orders`	Order transactions	order_id, customer_id, date, status
`order_items`	Line items per order	item_id, order_id, product_id, qty

💼 7 Business Queries & Insights

Query 1 — Total Revenue Generated

SELECT 
    SUM(p.price * oi.quantity) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id;

💡 Tracks overall financial performance — helps management evaluate growth trends and pricing strategy.

Query 2 — Top 5 Best Selling Products

SELECT 
    p.product_name,
    SUM(oi.quantity) AS total_units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_units_sold DESC
LIMIT 5;

💡 Identifies top performers — helps focus inventory planning, marketing, and supply chain optimization.

Query 3 — Top 10 High Value Customers

SELECT 
    c.customer_name,
    SUM(p.price * oi.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 10;

💡 Pareto Principle — top 20% customers drive 80% revenue. Target these for loyalty programs.

Query 4 — Monthly Revenue Trend

SELECT 
    DATE_FORMAT(o.order_date,'%Y-%m') AS month,
    SUM(p.price * oi.quantity) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY month
ORDER BY month;

💡 Reveals seasonality patterns — helps plan promotions during slow months and stock up for peaks.

Query 5 — Customer Segmentation (CASE WHEN)

SELECT 
    c.customer_name,
    SUM(p.price * oi.quantity) AS total_spent,
    CASE 
        WHEN SUM(p.price * oi.quantity) > 500    THEN 'High Value'
        WHEN SUM(p.price * oi.quantity) >= 200   THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name;

💡 Enables personalized marketing — different campaigns for High, Medium, and Low value segments.

Query 6 — Customer Lifetime Value (CTE)

WITH customer_spending AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        SUM(p.price * oi.quantity) AS lifetime_value
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY c.customer_id, c.customer_name
)
SELECT * FROM customer_spending
ORDER BY lifetime_value DESC;

💡 High CLV customers deserve retention strategies, premium service, and loyalty rewards.

Query 7 — Product Revenue Ranking (Window Function)

SELECT 
    p.product_name,
    SUM(p.price * oi.quantity) AS total_revenue,
    RANK() OVER (
        ORDER BY SUM(p.price * oi.quantity) DESC
    ) AS product_rank
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

💡 Rankings help prioritize inventory investment and identify which products deserve prime shelf space.

🧠 SQL Concepts Used

Concept	Used In
✅ DDL — CREATE DATABASE, TABLE	Database Setup
✅ DML — INSERT, SELECT	Data Operations
✅ JOINS — Multi-table queries	All 7 Queries
✅ GROUP BY & ORDER BY	Queries 1, 2, 3, 4
✅ CASE WHEN — Conditional logic	Query 5
✅ CTE — Common Table Expression	Query 6
✅ Window Functions — RANK() OVER	Query 7
✅ Aggregate Functions — SUM, COUNT	Throughout
✅ DATE Functions — DATE_FORMAT	Query 4
✅ Foreign Keys — Relational integrity	Table Design

📂 Project Structure

Ecommerce-SQL-Analysis/
│
└── ecommerce_analysis.sql
    ├── Database Creation    → CREATE DATABASE & USE
    ├── Table Creation       → 4 tables with foreign keys
    ├── Data Insertion       → 1000+ synthetic records
    └── Business Queries     → 7 advanced business queries