Top N per group

Sum revenue per product first, then rank the products inside each category and keep rank 3 or lower. The order of those two steps is where people go wrong.

Query

WITH product_revenue AS (
  SELECT p.category, p.product_id, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.product_id
)
SELECT category, product_id, revenue
FROM (
  SELECT *,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
) t
WHERE rnk <= 3
ORDER BY category, rnk;

If you rank before aggregating, you rank individual order lines, not products.

Choosing the ranking function with ties

Suppose a category has revenues 500, 400, 400, 300, 100.

revenue   ROW_NUMBER   RANK   DENSE_RANK
500          1           1        1
400          2           2        2
400          3           2        2
300          4           4        3
100          5           5        4

What each filter keeps.

• ROW_NUMBER <= 3 returns 500, 400, 400 and silently drops the 300. Which of two equal products gets cut is random.
• RANK <= 3 returns 500, 400, 400. The next rank is 4.
• DENSE_RANK <= 3 returns 500, 400, 400, 300: the top three distinct values, so four rows.

Pick based on what the business wants. "Exactly three products per category" needs ROW_NUMBER with a stable tie-breaker such as product_id. "Everyone who is in the top 3 by revenue" wants RANK or DENSE_RANK.

Why not LIMIT

LIMIT 3 cuts the whole result, not each group. People try to put LIMIT 3 inside a correlated subquery for each category. It is slow, not supported in every place, and gets worse as the number of categories grows. Window functions do the work in one pass.

Engine shortcut

On engines with QUALIFY, replace the outer query: QUALIFY DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3.