🚀 SQL Project Series #1

E-Commerce Sales Analysis Project 🛒

Build a real-world SQL project from scratch and learn the SQL skills required for Data Analyst interviews.

🎯 Business Objectives

✅ Analyze total sales and revenue

✅ Identify top-selling products

✅ Find the best-performing product categories

✅ Calculate monthly sales trends

✅ Identify repeat customers

✅ Find inactive customers

✅ Calculate Average Order Value (AOV)

✅ Calculate Customer Lifetime Value (CLV)

✅ Analyze customer purchasing behavior

📂 Step 1: Create Database

CREATE DATABASE ecommerce_db;

USE ecommerce_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100),

gender VARCHAR(10),

city VARCHAR(50),

signup_date DATE

);

📂 Step 3: Create Products Table

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(100),

category VARCHAR(50),

price DECIMAL(10,2)

);

📂 Step 4: Create Orders Table

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

order_status VARCHAR(30),

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

);

📂 Step 5: Create Order_Items Table

CREATE TABLE order_items (

order_item_id INT PRIMARY KEY,

order_id INT,

product_id INT,

quantity INT,

unit_price DECIMAL(10,2),

FOREIGN KEY (order_id)

REFERENCES orders(order_id),

FOREIGN KEY (product_id)

REFERENCES products(product_id)

);

📂 Step 6: Insert Sample Customers

INSERT INTO customers VALUES

(1,'Rahul','Male','Mumbai','2025-01-10'),

(2,'Priya','Female','Delhi','2025-01-15'),

(3,'Amit','Male','Pune','2025-02-01'),

(4,'Sneha','Female','Bangalore','2025-02-10'),

(5,'Rohan','Male','Hyderabad','2025-03-05');

📂 Step 7: Insert Sample Products

INSERT INTO products VALUES

(101,'Laptop','Electronics',65000),

(102,'Headphones','Electronics',2500),

(103,'Office Chair','Furniture',7000),

(104,'Keyboard','Electronics',1800),

(105,'Water Bottle','Home',600);

📂 Step 8: Insert Sample Orders

INSERT INTO orders VALUES

(1001,1,'2025-03-01','Delivered'),

(1002,2,'2025-03-03','Delivered'),

(1003,1,'2025-03-10','Delivered'),

(1004,3,'2025-03-15','Cancelled'),

(1005,4,'2025-03-20','Delivered');

📂 Step 9: Insert Sample Order Items

INSERT INTO order_items VALUES

(1,1001,101,1,65000),

(2,1001,102,2,2500),

(3,1002,103,1,7000),

(4,1003,104,1,1800),

(5,1004,105,3,600),

(6,1005,101,1,65000);

🧠 SQL Concepts You'll Practice

✔ DDL Commands

✔ DML Commands

✔ Primary & Foreign Keys

✔ Joins

✔ Aggregate Functions

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ Subqueries

✔ CTEs

✔ Window Functions

✔ Date Functions

📊 Business KPIs You Can Build

📈 Total Revenue

📈 Total Orders

📈 Total Customers

📈 Average Order Value (AOV)

📈 Revenue by Product Category

📈 Monthly Sales Trend

📈 Daily Sales Trend

📈 Top 10 Selling Products

📈 Top 10 Customers by Revenue

📈 Revenue by City

📈 Revenue by Gender

📈 Customer Lifetime Value (CLV)

📈 Repeat Purchase Rate

📈 Customer Retention Rate

📈 Customer Churn Rate

📈 Average Products per Order

📈 Order Cancellation Rate

📈 Delivered vs Cancelled Orders

📈 Best Selling Category

📈 Worst Selling Category

📈 Most Expensive Product Sold

📈 Highest Revenue Month

📈 Customer Acquisition by Month

📈 New vs Returning Customers

📈 Product-wise Revenue

📈 Category-wise Revenue Contribution

🎯 Double Tap ❤️ For Part-2