Find the top three products in each category.

To retrieve the top three products per category, you can use a window function such as ROW_NUMBER() to rank products within each category based on a metric (e.g., sales, rating). The query partitions by category, orders by the metric descending, assigns a row number, and then filters for row_number <= 3. Example:

SELECT product_id, category_id, sales

FROM (

SELECT p.product_id, p.category_id, p.sales,

     ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.sales DESC) AS rn

FROM products p

) sub

WHERE rn <= 3;

If your database lacks window functions, you can achieve the same with a correlated subquery or a join to a derived table that selects the top N per group using LIMIT and GROUP BY. Complexity is O(n log n) for sorting within each group, but the database engine optimizes it. This approach scales well for large datasets and is portable across most SQL dialects.