Solving 45 real-world e-commerce business problems using SQL 🚀
This project demonstrates end-to-end data analysis using SQL on an e-commerce dataset. It focuses on deriving actionable business insights using advanced SQL techniques.
- 📈 Customer Retention using Cohort Analysis
- 💰 Revenue Optimization using Pareto (80/20) Analysis
⚠️ Detection of abnormal patterns via Anomaly Detection- 📦 Inventory performance using Turnover Analysis
- 🧠 Advanced SQL using Window Functions & CTEs
- Joins & Aggregations
- Subqueries & CTEs
- Window Functions (
RANK,ROW_NUMBER,NTILE) - Cohort Analysis
- Pareto Analysis
- KPI Metrics (AOV, Revenue, Retention)
- 🔥 Top 20% products contribute ~80% of total revenue
- 📉 Customer retention drops significantly after Month 1
- 📦 Identified slow-moving inventory using turnover ratio
⚠️ Detected high-quantity anomalies in orders
sql-ecommerce-analytics/
│
├── easy/ # Basic SQL queries
├── medium/ # Business logic problems
├── hard/ # Window functions & analytics
├── advanced/ # Cohort, Pareto, anomalies
├── datasets/ # Schema & sample data
└── docs/ # Explanations & notesThis ER diagram represents relationships between customers, orders, products, inventory, and logistics systems.
- SQL (MySQL)
- Excel (Power Query, VBA)
- (Optional) Power BI
- Execute
schema.sql - Insert sample data
- Run queries from each folder
(Add screenshots here for better impact)
- Cohort retention table
- Pareto revenue distribution
- Query outputs
- Identifies high-value customers and products
- Helps optimize inventory and reduce dead stock
- Detects anomalies in ordering patterns
- Supports data-driven decision-making
SQL • Data Analysis • Power BI • Cohort Analysis • Business Intelligence
Shubham Tripathi Data Analyst | SQL | Business Analytics
This repository is for learning and reference purposes. Direct modifications are restricted.