A complete SQL case study modeling an e-commerce platform with customers, sellers, products, orders, shipping, inventory, and payments. Designed using clean schemas, data validation checks, and comprehensive analytical queries for advanced SQL analysis.
Tech Stack: MySQL · SQL Analytics · Relational Modelling · Stored Procedures Author: Revanth Manepalli
This project models a simplified version of the Amazon marketplace. It includes:
- A complete database schema with 8 interconnected tables
- Detailed data-quality validation checks
- A full relational model connecting sellers, customers, products, and order flows
- Sixteen comprehensive analytical SQL queries using advanced techniques (CTEs, Window Functions, Subqueries)
- A production-ready stored procedure for processing sales transactions
The System covers:
- Customers - User profiles and demographics
- Sellers - Marketplace vendor management
- Products - Product catalog with pricing and categories
- Orders + Order Items - Transactional data with line items
- Shipping - Delivery tracking and provider performance
- Inventory - Stock management across warehouses
- Payments - Transaction status and processing
- Category - Product classification hierarchy
Each table is connected through clear relational links, forming a robust schema suitable for business intelligence and operational analytics.
A straightforward and well-organized layout for easy navigation.
zomato-sql-project/
│
├── README.md
│
├── EER Diagram.png
│
├── sql/
│ ├── schema.sql
│ ├── data_checks.sql
│ └── analysis.sql
│
├── Datasets/
│ ├── category.csv
│ ├── customers.csv
│ ├── sellers.csv
│ ├── products.csv
│ ├── orders.csv
│ ├── order_items.csv
│ ├── payments.csv
│ ├── shipping.csv
│ └── inventory.csv
│
├── Results/
│ ├── Problem 1.csv
│ ├── Problem 2.csv
│ ├── Problem 3.csv
│ ├── Problem 4.csv
│ ├── Problem 5.csv
│ ├── Problem 6.csv
│ ├── Problem 7.csv
│ ├── Problem 8.csv
│ ├── Problem 9.csv
│ ├── Problem 10.csv
│ ├── Problem 11.csv
│ ├── Problem 12.csv
│ ├── Problem 13.csv
│ ├── Problem 14.csv
│ ├── Problem 15.csv
│ └── Problem 16.csv
The model clearly establishes:
- Category ↔ Products
- Customers ↔ Orders
- Sellers ↔ Orders
- Orders ↔ Order Items
- Orders ↔ Payments
- Orders ↔ Shipping
- Products ↔ Inventory
This enables in-depth marketplace analytics.
The project includes 16 comprehensive SQL problems covering sales analytics, customer segmentation, inventory management, and performance tracking.
Business Question: What are our top-performing products by revenue?
Approach: Calculate total sales (quantity × price) for each product, aggregate across all orders, and rank by revenue. Includes order count for frequency analysis.
Key Insights: Identifies revenue drivers and popular products for inventory planning.
Business Question: How does each product category contribute to overall revenue?
Approach: Sum sales by category and calculate percentage contribution to total revenue. Uses subquery for total revenue denominator.
Key Insights: Reveals category performance for strategic merchandising decisions.
Business Question: Who are our most loyal customers and what's their average order value?
Approach: Filter customers with >5 orders, calculate average order value (total sales ÷ order count), and segment by loyalty.
Key Insights: Identifies high-value loyal customers for retention programs.
Business Question: How are sales trending month-over-month?
Approach: Aggregate monthly sales for the last 18 months, use LAG() window function to compare with previous month.
Key Insights: Tracks growth trends and seasonality patterns.
Business Question: Which registered customers haven't made a purchase?
Approach: Find customers with no matching records in orders table using NOT IN subquery.
Key Insights: Identifies acquisition funnel drop-off for reactivation campaigns.
Business Question: What's the bestselling category in each geographic region?
Approach: Rank categories by sales within each state using RANK() window function, filter for top ranks.
Key Insights: Enables localized merchandising and inventory distribution.
Business Question: What is each customer's total lifetime value and how do they rank?
Approach: Calculate cumulative revenue per customer, rank using DENSE_RANK() by descending value.
Key Insights: Segments customers by value for targeted marketing spend.
Business Question: Which products need immediate restocking?
Approach: Filter inventory where stock levels fall below safety threshold (15 units).
Key Insights: Proactive inventory management to prevent stockouts.
Business Question: Which orders experienced extreme shipping delays?
Approach: Calculate days between order and shipping dates, filter for delays >500 days.
Key Insights: Identifies fulfillment process issues and customer service cases.
Business Question: What's the breakdown of payment statuses across orders?
Approach: Group orders by payment status, calculate counts and percentages.
Key Insights: Monitors payment processing health and fraud patterns.
Business Question: How do our top sellers perform in terms of order completion?
Approach: Identify top 5 sellers by revenue, then analyze their delivered vs cancelled orders with success rate calculation.
Key Insights: Evaluates seller reliability for partnership decisions.
Business Question: Which products are most profitable considering cost of goods?
Approach: Calculate profit (revenue - COGS) and profit margin percentage, rank by margin.
Key Insights: Identifies high-margin products for promotion and low-margin items for review.
Business Question: Which sellers have been inactive and when was their last sale?
Approach: Identify sellers with no sales in last 15 months, retrieve their last sale details.
Key Insights: Supports seller relationship management and reactivation efforts.
Business Question: Who are the best customers in each state by order frequency?
Approach: Rank customers within each state by order count using RANK(), filter top 5.
Key Insights: Enables regional customer appreciation programs and local marketing.
Business Question: How do different shipping providers perform?
Approach: Aggregate orders handled, total sales value, and average return processing time by provider.
Key Insights: Informs shipping partner selection and contract negotiations.
Business Question: Which products show concerning revenue decline year-over-year?
Approach: Compare product revenue between 2023 and 2024, calculate decline ratio, highlight top decliners.
Key Insights: Early warning system for product lifecycle management and marketing interventions.
Each query is written clearly, using CTEs and window functions for readability and performance.
Purpose: Implements a complete sales transaction workflow with inventory validation and atomic operations.
Business Logic Flow:
- Input Validation: Accepts order details including customer, seller, product, and quantity
- Stock Check: Verifies sufficient inventory before processing
- Transaction Processing:
- Creates order record with current date
- Adds order item with calculated total sale amount
- Updates inventory stock levels
- Feedback: Returns clear success/failure messages
Key Features:
- Atomic Operations: Ensures data consistency across multiple tables
- Inventory Control: Prevents overselling with real-time stock checks
- Audit Trail: Maintains complete transaction history
- User Feedback: Clear messages for both successful and failed transactions
Use Case: This procedure mimics real-world e-commerce checkout systems, making it production-ready for integration with frontend applications.
-
Clone the repository:
git clone https://github.com/Revanth-144/Amazon-Style-E-Commerce-Order-Management-System-SQL-Database-Project-.git
-
Navigate to the SQL folder.
-
Run the files in this order:
- schema.sql
- data_checks.sql
- analysis.sql
- Test the stored procedure
add_saleswith sample inputs.
CALL add_sales(
p_order_id => ,
p_customer_id => ,
p_seller_id => ,
p_order_item => ,
p_product_id => ,
p_quantity =>
);- Use your preferred MySQL client (Workbench / CLI / DBeaver).
This project demonstrates proficiency in:
- Advanced SQL: CTEs, Window Functions, Complex Joins
- Business Intelligence: Sales analytics, customer segmentation, inventory optimization
- Data Modeling: Relational database design with referential integrity
- Production SQL: Stored procedures for business logic encapsulation
- Performance: Optimized queries with proper indexing considerations
The analyses provide actionable insights for:
- Revenue Optimization: Identify top products and categories
- Customer Retention: Segment customers by value and loyalty
- Inventory Management: Prevent stockouts and optimize levels
- Supplier/Vendor Management: Evaluate seller and shipping partner performance
- Operational Efficiency: Monitor fulfillment and payment processing
Potential extensions to this project:
- Real-time Dashboard: Connect to BI tools (Tableau, Power BI)
- Predictive Analytics: Forecasting models for demand prediction
- Personalization Engine: Recommendation system based on purchase patterns
- Automated Reporting: Scheduled email reports of key metrics
- API Integration: REST API layer for application integration
🎯 This project provides a complete foundation for e-commerce analytics and operations management.