Write a SQL query that returns the top three products by paid quantity in every category for a requested period.

Category A has quantities 20, 20, 15, and 4. The two products tied at 20 are ordered by product id, followed by 15. Exactly three rows are returned.

• Only paid orders in [FromUtc, ToUtc) contribute.
• Return at most three rows per category.
• The tie-breaker must make the selected three deterministic.
• Products with no sales do not appear.

I first aggregate to one row per category and product. Ranking raw order lines would rank purchases, not products. I then use ROW_NUMBER partitioned by category and order by quantity descending plus product id. ROW_NUMBER implements an exact three-row policy; if the product requirement were to include every tie, I would choose DENSE_RANK instead.

WITH ProductSales AS
(
    SELECT
        p.CategoryId,
        p.ProductId,
        p.Name,
        SUM(ol.Quantity) AS PaidQuantity
    FROM dbo.Orders AS o
    INNER JOIN dbo.OrderLines AS ol ON ol.OrderId = o.OrderId
    INNER JOIN dbo.Products AS p ON p.ProductId = ol.ProductId
    WHERE o.Status = 'Paid'
      AND o.PaidAtUtc >= @FromUtc
      AND o.PaidAtUtc < @ToUtc
    GROUP BY p.CategoryId, p.ProductId, p.Name
),
RankedProducts AS
(
    SELECT
        CategoryId,
        ProductId,
        Name,
        PaidQuantity,
        ROW_NUMBER() OVER
        (
            PARTITION BY CategoryId
            ORDER BY PaidQuantity DESC, ProductId
        ) AS CategoryRank
    FROM ProductSales
)
SELECT CategoryId, ProductId, Name, PaidQuantity, CategoryRank
FROM RankedProducts
WHERE CategoryRank <= 3
ORDER BY CategoryId, CategoryRank;

The joins and aggregation dominate the work; filters should narrow paid orders before ranking. Useful indexes depend on table sizes but commonly begin with the order date/status access path and the foreign keys used by line and product joins.

• A category with fewer than three selling products.
• Several products tied at the third quantity.
• A product appears on many order lines.
• Categories with no paid sales.

• Use ties around rank three to verify the exact-row policy.
• Give one product many lines so aggregation must happen before ranking.
• Include cancelled orders with large quantities.

• Ranking order lines before aggregating products.
• Using a global TOP (3) instead of partitioning by category.
• Leaving tie behavior unspecified.
• Using DENSE_RANK when the requirement says at most three rows.