This project builds a normalized retail database using MySQL and performs advanced SQL analytics on 10,000+ e-commerce transactions to generate business KPIs and strategic insights.
The flat dataset was transformed into a relational schema to simulate enterprise-grade data modeling.
The original flat dataset was normalized into four relational tables:
- customers
- products
- orders
- order_items
Foreign key relationships were implemented to ensure referential integrity.
- Total revenue calculation
- Monthly revenue trend analysis
- Month-over-Month (MoM) growth using LAG window function
- Country revenue ranking using RANK()
- Customer segment contribution %
- Top 5 products by revenue
- Customer lifetime value (CLV)
- Category-wise revenue contribution
- INNER JOIN
- GROUP BY & Aggregations
- Common Table Expressions (CTE)
- Window Functions (LAG, RANK)
- Revenue contribution percentage calculations
- Date formatting & time-series grouping
.png)
.png)
.png)
.png)
- Total Revenue: ~$1.63M
- Strong MoM volatility observed, including a -44.5% drop in Feb 2022 and +51.3% rebound in Mar 2022.
- Indicates seasonal demand behavior.
- United States leads revenue generation (~$431K).
- India is second (~$280K).
- Revenue concentration suggests potential expansion opportunities.
- Low-Value segment contributes 65.6% of revenue.
- High-Value customers contribute only 8.8%.
- Upselling and retention strategies may improve profitability.
- Electronics category dominates top revenue positions.
- Category concentration suggests dependency on electronics products.
- MySQL 8
- MySQL Workbench
- Python (Pandas)
- CSV-based data pipeline
retail-bi-sql-engine/ │ ├── data/ ├── sql/ ├── scripts/ ├── outputs/ ├── README.md └── .gitignore
This project demonstrates end-to-end data pipeline capability:
- Data normalization
- Relational modeling
- Advanced SQL analytics
- KPI engineering
- Strategic business insight generation