SQL_RetailSales_Project

🛒 Retail Sales Analysis System

A Comprehensive SQL Data Engineering & Analysis Project

---

💎 Project Overview

This project focuses on building a robust Retail Sales Database from scratch. It covers everything from Schema Design and Data Normalization to Complex Analytical Querying. The system manages multi-branch operations, inventory tracking, and sales performance metrics.

---

🏗️ Database Architecture (Schema)

The database architecture is designed with 7 core entities to ensure data integrity and minimize redundancy:

• 📍 Branches: Tracking locations across Egypt (Cairo, Giza, Alexandria).
• 👥 Employees: Managing staff roles and branch assignments.
• 🛍️ Customers: Maintaining detailed client profiles.
• 📦 Products: Categorized inventory (Electronics, Furniture, Accessories).
• 📊 Sales: Centralized transaction headers.
• 📝 SaleDetails: Granular item-level transaction data.
• 🔋 Inventory: Real-time stock level management.

---

🚀 Advanced SQL Features Implemented

In this project, I demonstrated proficiency in:

• Complex Joins: Utilizing LEFT, RIGHT, and FULL OUTER JOINs.
• Window Functions: Implementing RANK(), DENSE_RANK(), and ROW_NUMBER().
• Data Aggregation: Grouping data with HAVING and SUM/AVG/COUNT filters.
• Reporting Views: Created inventorySummary and SalesReport for instant business insights.
• CTEs & Subqueries: Handling multi-level data extraction.

---

📊 Business Insights (Samples)

🏆 Top Selling Product (Window Function)

SELECT ProductName, SUM_QUANT 
FROM (
    SELECT P.ProductName, SUM(S.Quantity) AS SUM_QUANT,
    ROW_NUMBER() OVER(ORDER BY SUM(S.Quantity) DESC) AS RN
    FROM Product P JOIN SaleDetails S ON P.ProductID = S.ProductID
    GROUP BY P.ProductName
) X WHERE RN = 1;