Performance 1-4

-- Step 1: Create the Database
CREATE DATABASE retail_sales;

-- Step 2: Use the Database
USE retail_sales;

-- Step 3: Create the Sales Table
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(50),
    Region VARCHAR(50),
    CustomerName VARCHAR(50),
    TotalAmount DECIMAL(10, 2)
);

-- Step 4: Insert Sample Data into the Sales Table
INSERT INTO Sales (SaleID, ProductName, Category, Region, CustomerName, TotalAmount) VALUES
(1, 'Laptop', 'Electronics', 'North', 'John Doe', 1500.00),
(2, 'T-Shirt', 'Clothing', 'South', 'Jane Smith', 20.00),
(3, 'Smartphone', 'Electronics', 'East', 'Alice Johnson', 800.00),
(4, 'Shoes', 'Footwear', 'West', 'Michael Brown', 60.00),
(5, 'Headphones', 'Electronics', 'North', 'Chris Lee', 120.00),
(6, 'Dress', 'Clothing', 'South', 'Emma White', 45.00),
(7, 'Tablet', 'Electronics', 'East', 'Sophia Miller', 600.00),
(8, 'Sandals', 'Footwear', 'North', 'Oliver Green', 30.00),
(9, 'Jacket', 'Clothing', 'West', 'Mia Taylor', 90.00),
(10, 'Watch', 'Accessories', 'East', 'Liam Wilson', 150.00);

-- Step 5: Query to Find Top-Selling Products in Each Category
SELECT Category, ProductName, SUM(TotalAmount) AS TotalSales
FROM Sales
GROUP BY Category, ProductName
ORDER BY Category, TotalSales DESC;

-- Step 6: Query to Find Regions with the Highest Sales Revenue
SELECT Region, SUM(TotalAmount) AS TotalRevenue
FROM Sales
GROUP BY Region
ORDER BY TotalRevenue DESC;


-- Step 7: Query to Find Top 5 Customers Based on Total Purchase Amount
SELECT CustomerName, SUM(TotalAmount) AS TotalPurchase
FROM Sales
GROUP BY CustomerName
ORDER BY TotalPurchase DESC
LIMIT 5;