-- Query1
SELECT Category, COUNT(ProductId) AS NumberOfProducts
FROM Product
GROUP BY Category;

-- Query2
SELECT Category, TO_CHAR(AVG(ExFactoryPrice), '99999999990.00') AS AveragePrice
FROM Product
GROUP BY Category
ORDER BY AveragePrice DESC;

-- Query3
SELECT ProductId, ProductName
FROM Product P, Company C
WHERE P.CompanyId = C.CompanyId AND (C.State = 'CA' OR C.State = 'IN')
ORDER BY ProductId ASC;

-- Query4
SELECT RI.RetailerId, O.ProductId, (SUM(Count) - RI.TotalStock) AS PrepareNumber
FROM Orders O, RetailerInventory RI
WHERE O.RetailerId = RI.RetailerId AND O.ProductId = RI.ProductId AND O.Status = 'PENDDING'
GROUP BY RI.RetailerId, O.ProductId, RI.TotalStock -- Check this GROUP BY
ORDER BY RI.RetailerId ASC;

-- Query5
SELECT ProductName, NumberOfItemsSold
FROM (
    SELECT P.ProductName, NumberOfItemsSold, RANK()
        OVER (ORDER BY NumberOfItemsSold DESC) AS Score
    FROM (
        SELECT ProductId, SUM(Count) AS NumberOfItemsSold
        FROM Orders
        GROUP BY ProductId
    ) S, Product P
    WHERE S.ProductId = P.ProductId
)
WHERE Score <= 3
ORDER BY NumberOfItemsSold DESC;

-- Query6
SELECT FirstName, LastName, TO_CHAR(ROUND(TotalAmount, 2), '99999999990.00')
FROM (
    SELECT CustomerId, TotalAmount, RANK()
        OVER (ORDER BY TotalAmount DESC) AS Score
        FROM (
            SELECT CustomerId, SUM(Count * UnitPrice) AS TotalAmount
            FROM Orders
            GROUP BY CustomerId
        )
) T, Customer C
WHERE Score <= 3 AND C.CustomerId = T.CustomerId
ORDER BY TotalAmount DESC;

-- Query7
SELECT O.RetailerId, R.RetailerName, O.NumberOfOrders
FROM (
    SELECT RetailerId, NumberOfOrders, RANK()
        OVER (ORDER BY NumberOfOrders DESC) AS Score
        FROM (
            SELECT RetailerId, COUNT(OrderId) AS NumberOfOrders
            FROM Orders
            GROUP BY RetailerId
        )
) O, Retailer R
WHERE O.Score <= 2 AND R.RetailerId = O.RetailerId
ORDER BY O.NumberOfOrders DESC;

-- Query8
SELECT P.RetailerId, R.RetailerName, TO_CHAR(ROUND(P.NetProfit, 2), '99999999990.00')
FROM (
    SELECT RetailerId, NetProfit, RANK()
    OVER (ORDER BY NetProfit DESC) AS Score
    FROM (
        SELECT RetailerId, SUM(Count * (UnitPrice - ExFactoryPrice)) AS NetProfit
        FROM Orders O, Product P
        WHERE P.ProductId = O.ProductId
        GROUP BY O.RetailerId
    )
) P, Retailer R
WHERE P.Score <= 2 AND P.RetailerId = R.RetailerId
ORDER BY NetProfit DESC;

-- Query9
SELECT T.CompanyId, C.CompanyName, T.TotalCount
FROM (
    SELECT CompanyId, TotalCount, RANK()
    OVER (ORDER BY TotalCount DESC) AS Score
    FROM (
        SELECT P.CompanyId, SUM(O.Count) AS TotalCount
        FROM Product P, Orders O
        WHERE P.ProductId = O.ProductId
        GROUP BY P.CompanyId
    )
) T, Company C
WHERE T.Score <= 3 AND T.CompanyId = C.CompanyId
ORDER BY TotalCount DESC;

-- Query10
SELECT D.RetailerId, R.RetailerName, NumberOfDistinctCustomers
FROM (
    SELECT RetailerId, NumberOfDistinctCustomers, RANK()
    OVER (ORDER BY NumberOfDistinctCustomers DESC) AS Score
    FROM (
        SELECT RetailerId, COUNT(DISTINCT CustomerId) AS NumberOfDistinctCustomers
        FROM Orders
        GROUP BY RetailerId
    )
) D, Retailer R
WHERE D.Score <= 2 AND D.RetailerId = R.RetailerId
ORDER BY NumberOfDistinctCustomers DESC;