Post 🚀 SQL Project Series #1

🚀 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