E-Commerce SQL Analytics | MySQL Project

📌 Project Overview

End-to-end SQL analytics layer built over a 50,000-row e-commerce database using MySQL. Delivers a full business KPI suite including revenue analysis, customer retention, AOV, and category performance.

Key Result: Query runtime reduced by 60% through index optimization and execution plan analysis.

---

🎯 Business Questions Answered

• What is the monthly and yearly revenue trend?
• Which product categories contribute the most to revenue?
• What is the customer repeat purchase rate?
• What is the Average Order Value (AOV) by segment?
• Which customers are most valuable (RFM analysis)?
• How do sales rank across regions and categories?

---

🛠 Tools & Technologies

MySQL CTEs Window Functions Subqueries Index Optimization Execution Plans

---

🗃️ Database Schema

Table	Description
customers	Customer demographics and registration info
orders	Order headers with date, status, customer_id
order_items	Line items with product, quantity, price
products	Product catalog with category and pricing
categories	Product category hierarchy

---

📊 Key SQL Techniques Used

Window Functions

-- Revenue rank by category
SELECT category_name,
       SUM(amount) AS revenue,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS revenue_rank
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY category_name;

CTEs for Repeat Rate

WITH customer_orders AS (
  SELECT customer_id, COUNT(order_id) AS total_orders
  FROM orders
  GROUP BY customer_id
)
SELECT 
  ROUND(SUM(CASE WHEN total_orders > 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS repeat_rate_pct
FROM customer_orders;

LAG for Month-over-Month Growth

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / LAG(revenue) OVER (ORDER BY month), 2) AS mom_growth_pct
FROM monthly_revenue;

---

📈 KPIs Delivered

KPI	Result
Total Revenue	Calculated via multi-table joins
Repeat Purchase Rate	CTE-based cohort logic
Average Order Value (AOV)	By customer segment
Category Contribution %	Window RANK + PERCENT
MoM Revenue Growth	LAG/LEAD window functions
Query Performance Improvement	60% runtime reduction via indexing

---

⚡ Performance Optimization

• Added composite indexes on (customer_id, order_date) and (product_id, category_id)
• Analyzed execution plans using EXPLAIN to identify full table scans
• Replaced correlated subqueries with CTEs for readability and speed
• Result: 60% reduction in reporting query runtime

---

👩‍💻 Author

Gauri Lingshetti — Data Analyst | MCA, Bharati Vidyapeeth University (8.94 CGPA) LinkedIn | GitHub