Sales Analytics & Query Optimization Project
Overview
This project simulates a real-world retail sales system using SQL Server.
It focuses on data modeling, analytics, performance optimization, and business insights generation.
Database Design The system consists of 4 main tables:
- Customers
- Products
- Orders
- Order Items
Relationships are normalized using primary and foreign keys to ensure data integrity.
Key Features
- Revenue calculation with discount handling
- Monthly revenue tracking
- Active customer analysis
- Top-selling products identification
- Regional performance breakdown
- High-value customer segmentation (CTE)
- Query optimization using indexing
Performance Optimization Implemented indexes on:
- order_date
- customer_id
- order_id
- (status, order_date)
to improve query execution performance.
Analytics & Views Created SQL views for:
- Monthly Revenue (
vw_monthly_revenue) - Active Customers (
vw_active_customers) - Top Selling Products (
vw_top_selling_products)
Business Insights
- Seasonal sales spikes detected in Nov/Dec
- Regional revenue comparison
- Product category performance analysis
- Customer spending segmentation
Tech Stack
- SQL Server
- T-SQL
- Window Functions
- CTEs
- Indexing & Optimization
Esraa Abdelazeem