Retail Sales Analytics Using MySQL - Data Analysis in 2025

🗂️ Project Title: Retail Sales Analytics Using MySQL

🎯 Objective:

Analyze retail sales data to gain insights into products, customers, regions, and revenue trends.

---

📁 1. Project Structure

We’ll create the following tables:

• Customers
• Products
• Orders
• OrderDetails
• Regions

---

🏗️ 2. SQL Table Creation & Sample Data

🧾 Customers Table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Gender CHAR(1),
    Age INT,
    RegionID INT
);

INSERT INTO Customers VALUES
(1, 'Amit Sharma', 'M', 28, 1),
(2, 'Priya Mehta', 'F', 24, 2),
(3, 'Ravi Verma', 'M', 35, 3),
(4, 'Sonal Agarwal', 'F', 40, 1),
(5, 'Karan Patel', 'M', 22, 2);

---

📦 Products Table

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10,2)
);

INSERT INTO Products VALUES
(101, 'Laptop', 'Electronics', 55000),
(102, 'Smartphone', 'Electronics', 30000),
(103, 'Desk Chair', 'Furniture', 5000),
(104, 'Notebook', 'Stationery', 50),
(105, 'Pen Pack', 'Stationery', 100);

---

🧾 Orders Table

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    RegionID INT
);

INSERT INTO Orders VALUES
(1001, 1, '2025-06-01', 1),
(1002, 2, '2025-06-03', 2),
(1003, 3, '2025-06-04', 3),
(1004, 4, '2025-06-05', 1),
(1005, 5, '2025-06-06', 2);

---

📦 OrderDetails Table

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT
);

INSERT INTO OrderDetails VALUES
(1, 1001, 101, 1),
(2, 1001, 104, 5),
(3, 1002, 102, 1),
(4, 1003, 103, 2),
(5, 1004, 105, 3),
(6, 1005, 104, 10);

---

🌍 Regions Table

CREATE TABLE Regions (
    RegionID INT PRIMARY KEY,
    RegionName VARCHAR(100)
);

INSERT INTO Regions VALUES
(1, 'North India'),
(2, 'South India'),
(3, 'West India');

---

🔍 3. Data Analysis Tasks (With Solutions)

---

📊 Task 1: Total Revenue by Product

SELECT P.ProductName, SUM(OD.Quantity * P.Price) AS TotalRevenue
FROM OrderDetails OD
JOIN Products P ON OD.ProductID = P.ProductID
GROUP BY P.ProductName
ORDER BY TotalRevenue DESC;

---

👤 Task 2: Top Spending Customers

SELECT C.CustomerName, SUM(OD.Quantity * P.Price) AS TotalSpent
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
JOIN OrderDetails OD ON O.OrderID = OD.OrderID
JOIN Products P ON OD.ProductID = P.ProductID
GROUP BY C.CustomerName
ORDER BY TotalSpent DESC;

---

🌍 Task 3: Region-wise Sales

SELECT R.RegionName, SUM(OD.Quantity * P.Price) AS RegionalRevenue
FROM Orders O
JOIN OrderDetails OD ON O.OrderID = OD.OrderID
JOIN Products P ON OD.ProductID = P.ProductID
JOIN Regions R ON O.RegionID = R.RegionID
GROUP BY R.RegionName;

---

📅 Task 4: Daily Order Volume

SELECT OrderDate, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY OrderDate
ORDER BY OrderDate;

---

🗂️ Task 5: Product Category Sales Summary

SELECT Category, SUM(OD.Quantity * P.Price) AS CategoryRevenue
FROM Products P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY Category;

---

📈 4. Advanced Ideas (Optional Extension)

• Use window functions to calculate customer rank by total spending.

• Apply stored procedures to generate monthly reports.

• Use JOINs with filters for gender-wise sales data.

• Create pivot tables in Excel using exported data from MySQL.

---

📌 5. Tools Suggested

• MySQL Workbench or phpMyAdmin for running queries

• Excel or Tableau for visual analytics (optional)

• Jupyter Notebook + SQLAlchemy + Pandas (for Python integration)

---

Comments for More Projects