Problem 1

SELECT 
    Category, 
    COUNT(*) AS Product_Count
FROM Products
GROUP BY Category;

Problem 2

SELECT 
    Size, 
    AVG(Weight) AS Avg_Weight
FROM Products
GROUP BY Size;

Problem 3

SELECT 
    c.Customer_ID, 
    c.Customer_Name,
    SUM(s.Gross_Amount) AS Total_Sales
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
GROUP BY c.Customer_ID, c.Customer_Name;

Problem 4

SELECT 
    Category, 
    COUNT(*) AS Product_Count
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5;

Problem 5

SELECT 
    SKU_Code,
    MIN(Date) AS First_Sale_Date,
    MAX(Date) AS Last_Sale_Date
FROM Sales_Transactions
GROUP BY SKU_Code;

Problem 6

SELECT 
    EXTRACT(MONTH FROM Date) AS Month,
    AVG(Gross_Amount) AS Avg_Sale_Amount
FROM Sales_Transactions
GROUP BY EXTRACT(MONTH FROM Date)
HAVING COUNT(*) > 10;

Problem 7

SELECT 
    SKU_Code,
    COUNT(DISTINCT Sale_ID) AS Transaction_Count
FROM Sales_Transactions
GROUP BY SKU_Code
HAVING COUNT(DISTINCT Sale_ID) > 3;

Problem 8

SELECT 
    c.Customer_ID, 
    c.Customer_Name,
    SUM(s.Gross_Amount) AS Total_Spent
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
GROUP BY c.Customer_ID, c.Customer_Name
HAVING SUM(s.Gross_Amount) > 5000
ORDER BY Total_Spent DESC;

Problem 9

WITH TotalValue AS (
    SELECT SUM(i.Stock_Qty * p.TP_Price) AS total
    FROM Inventory i
    JOIN Pricing p ON i.SKU_Code = p.SKU_Code
)
SELECT 
    i.SKU_Code,
    (i.Stock_Qty * p.TP_Price) AS Inventory_Value,
    ROUND(((i.Stock_Qty * p.TP_Price) / t.total) * 100, 2) AS Percentage_Total
FROM Inventory i
JOIN Pricing p ON i.SKU_Code = p.SKU_Code
CROSS JOIN TotalValue t;

Problem 10

WITH OverallAvg AS (
    SELECT AVG(Weight) AS overall_avg FROM Products
)
SELECT 
    p.Size,
    AVG(p.Weight) AS Avg_Weight,
    ABS(AVG(p.Weight) - o.overall_avg) AS Overall_Avg_Difference
FROM Products p
CROSS JOIN OverallAvg o
GROUP BY p.Size, o.overall_avg
HAVING ABS(AVG(p.Weight) - o.overall_avg) > 0.1;

Problem 11

SELECT 
    SKU_Code,
    MIN(Gross_Amount) AS Min_Sale_Amount,
    MAX(Gross_Amount) AS Max_Sale_Amount,
    MAX(Gross_Amount) - MIN(Gross_Amount) AS Amount_Variation
FROM Sales_Transactions
GROUP BY SKU_Code
HAVING MAX(Gross_Amount) - MIN(Gross_Amount) > 500;

Problem 12

WITH OperationTotals AS (
    SELECT 
        Operation_Type,
        COUNT(*) AS Operation_Count,
        COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Warehouse_Ops) AS Percentage_Total
    FROM Warehouse_Ops
    GROUP BY Operation_Type
)
SELECT 
    Operation_Type,
    Operation_Count,
    ROUND(Percentage_Total, 2) AS Percentage_Total
FROM OperationTotals
WHERE Percentage_Total > 20;

Problem 13

SELECT 
    c.Customer_ID,
    c.Customer_Name,
    COUNT(DISTINCT p.Category) AS Distinct_Categories
FROM Customers c
JOIN Sales_Transactions s ON c.Customer_ID = s.Customer_ID
JOIN Products p ON s.SKU_Code = p.SKU_Code
GROUP BY c.Customer_ID, c.Customer_Name
HAVING COUNT(DISTINCT p.Category) >= 3;

Problem 14

WITH DailyCategorySales AS (
    SELECT 
        s.Date AS Sale_Date,
        p.Category,
        SUM(s.Gross_Amount) AS Daily_Sales
    FROM Sales_Transactions s
    JOIN Products p ON s.SKU_Code = p.SKU_Code
    GROUP BY s.Date, p.Category
),
TotalSales AS (
    SELECT SUM(Daily_Sales) AS total FROM DailyCategorySales
)
SELECT 
    d.Sale_Date,
    d.Category,
    d.Daily_Sales,
    ROUND((d.Daily_Sales / t.total) * 100, 2) AS Percentage_Total
FROM DailyCategorySales d
CROSS JOIN TotalSales t
ORDER BY d.Sale_Date;

Problem 15

WITH ProductAvg AS (
    SELECT 
        SKU_Code,
        AVG(Quantity) AS avg_qty
    FROM Sales_Transactions
    GROUP BY SKU_Code
)
SELECT 
    p.SKU_Code,
    p.avg_qty AS Avg_Quantity,
    MIN(s.Quantity) AS Min_Quantity,
    MAX(s.Quantity) AS Max_Quantity
FROM ProductAvg p
JOIN Sales_Transactions s ON p.SKU_Code = s.SKU_Code
GROUP BY p.SKU_Code, p.avg_qty
HAVING MAX(s.Quantity) <= 1.1 * p.avg_qty
   AND MIN(s.Quantity) >= 0.9 * p.avg_qty;