query.sql

WITH CategorySales AS (
    -- 1. Calculate total sales for each product within its category
    SELECT 
        p.ProductID,
        p.ProductName,
        c.CategoryName,
        SUM(od.Quantity * od.UnitPrice) AS TotalSales,
        -- 2. Use a window function to rank products within each category
        ROW_NUMBER() OVER (
            PARTITION BY c.CategoryID 
            ORDER BY SUM(od.Quantity * od.UnitPrice) DESC
        ) AS Rank
    FROM 
        Products p
    JOIN 
        Categories c ON p.CategoryID = c.CategoryID
    JOIN 
        OrderDetails od ON p.ProductID = od.ProductID
    GROUP BY 
        p.ProductID, p.ProductName, c.CategoryID, c.CategoryName
)
-- 3. Select the top 3 products from the CTE result
SELECT 
    CategoryName,
    ProductName,
    TotalSales
FROM 
    CategorySales
WHERE 
    Rank <= 3
ORDER BY 
    CategoryName, Rank;