This project demonstrates my ability to work directly with a relational database, write advanced SQL, and design an analytics-ready dataset from normalized source tables.
I didn’t analyze a prebuilt CSV.
I built the dataset myself.
Scenario:
ShopStream Global needed a unified dataset combining orders, customers, products, sellers, and reviews to support:
- Revenue forecasting
- Seller performance analysis
- Customer segmentation
The data lived inside a PostgreSQL database (ecommerce schema).
My task was to transform five normalized tables into one clean, structured flat file ready for ETL and analytics.
Schema: ecommerce
Core tables used:
orderscustomersproductssellersreviews
- Full exploration and validation of all five tables
- Revenue aggregation by seller
- Return rates by product category
- Average review score per seller
- A five-table join producing one row per order
- A CTE for structured query logic
- A window function ranking customers by total spend
- Automated dataset export using Python
Final output: