ecommerce_analysis.sql

-- ====================================
-- SQL E-Commerce Analysis Project
-- Author: Devesh Shukla
-- Tool: MySQL Workbench
-- ====================================

-- 1. Database Creation
CREATE DATABASE sql_business_project;
use sql_business_project;

-- 2. Table Creation
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100),
email VARCHAR(100),
signup_date DATE,
country VARCHAR(50)
);

CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
order_status VARCHAR(50),

FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
product_id INT,
quantity INT,

FOREIGN KEY (order_id)
REFERENCES orders(order_id),

FOREIGN KEY (product_id)
REFERENCES products(product_id)
);

-- 3. Data Insertion
INSERT INTO products (product_name, category, price) VALUES
('Laptop','Electronics',800),
('Headphones','Electronics',120),
('Smartphone','Electronics',700),
('T-shirt','Clothing',25),
('Jeans','Clothing',60),
('Sneakers','Footwear',90),
('Backpack','Accessories',50),
('Watch','Accessories',150),
('Coffee Maker','Home Appliances',80),
('Blender','Home Appliances',70);

SELECT * FROM products;

INSERT INTO customers (customer_name, email, signup_date, country)
SELECT 
CONCAT('Customer_', FLOOR(RAND()*10000)),
CONCAT('user', FLOOR(RAND()*100000), '@email.com'),
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY),
ELT(FLOOR(1 + RAND()*5),'USA','India','UK','Canada','Germany')
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t3;
 
 SELECT COUNT(*) FROM customers;
 
INSERT INTO orders (customer_id, order_date, order_status)
SELECT
FLOOR(1 + RAND()*1000),
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY),
ELT(FLOOR(1 + RAND()*3),'Completed','Pending','Cancelled')
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) c,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d;

SELECT COUNT(*) FROM orders;

INSERT INTO order_items (order_id, product_id, quantity)
SELECT
FLOOR(1 + RAND()*5000),
FLOOR(1 + RAND()*10),
FLOOR(1 + RAND()*5)
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) b,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) c,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) d;
 
 SELECT COUNT(*) FROM order_items;
 
-- =====================================================
-- BUSINESS QUERIES
-- =====================================================

-- Business Question:
-- What is the total revenue generated by the company from all orders?
  SELECT 
SUM(p.price * oi.quantity) AS total_revenue
FROM order_items oi
JOIN products p 
ON oi.product_id = p.product_id;
-- Explanation:
-- This query joins the products and order_items tables to calculate
-- the total revenue by multiplying product price with quantity sold.
-- Business Insight:
-- This metric shows the overall financial performance of the business.
-- Tracking total revenue helps management evaluate growth trends
-- and make strategic decisions about pricing, promotions, and inventory.

-- Business Question:
-- Which products generate the highest revenue for the business?
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;
-- Explanation:
-- This query calculates the total revenue generated by each product
-- by multiplying price with quantity sold and grouping by product name.
-- Business Insight:
-- Understanding top-performing products helps businesses focus on
-- inventory planning, marketing efforts, and supply chain optimization
-- to maximize profit.

-- Business Question:
-- Which customers contribute the most revenue to the company?
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;
-- Explanation:
-- This query joins customers, orders, order_items, and products tables
-- to calculate the total amount spent by each customer.
-- Business Insight:
-- A small percentage of customers usually generate a large portion
-- of revenue (Pareto Principle). Identifying these high-value customers
-- helps businesses design loyalty programs and targeted marketing campaigns.

-- Business Question:
-- How does revenue change month by month?
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;
-- Explanation:
-- This query groups sales data by month using order dates and
-- calculates the total revenue for each month.
-- Business Insight:
-- Monthly revenue trends help identify seasonality patterns,
-- sales growth, and potential slow periods. Businesses can use
-- this information to plan promotions, discounts, and marketing
-- strategies during low-performing months.

-- Business Question:
-- How can we categorize customers based on their total spending?
SELECT * FROM customers;
SELECT 
    c.customer_id,
    c.customer_name,
    SUM(p.price * oi.quantity) AS total_spent,
    CASE 
        WHEN SUM(p.price * oi.quantity) > 500 THEN 'High Value Customer'
        WHEN SUM(p.price * oi.quantity) BETWEEN 200 AND 500 THEN 'Medium Value Customer'
        ELSE 'Low Value Customer'
    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;
-- Explanation:
-- This query calculates total spending for each customer and 
-- classifies them into segments using CASE WHEN logic.
-- Business Insight:
-- Customer segmentation helps businesses identify their most valuable 
-- customers and design personalized marketing campaigns.

-- Business Question:
-- Which customers generate the highest lifetime revenue?
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;
-- Explanation:
-- This query calculates the total revenue generated by each customer
-- across all their orders using a Common Table Expression (CTE).
-- Business Insight:
-- Identifying high CLV customers helps businesses focus on retention,
-- loyalty programs, and premium services.

-- Business Question:
-- Which products generate the highest revenue?
SELECT 
    p.product_id,
    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;
-- Explanation:
-- This query calculates total revenue generated by each product
-- and ranks them using a window function.
-- Business Insight:
-- Identifying top-selling products helps businesses focus on 
-- inventory planning, marketing campaigns, and pricing strategy.