SELECT *
FROM (
  SELECT
    cat.name AS category,
    p.name AS product,
    SUM(oi.qty * oi.unit_price) AS revenue,
    RANK() OVER (PARTITION BY cat.cat_id ORDER BY SUM(oi.qty * oi.unit_price) DESC) AS rnk
  FROM categories cat
  JOIN products p ON cat.cat_id = p.cat_id
  JOIN order_items oi ON p.product_id = oi.product_id
  GROUP BY cat.cat_id, cat.name, p.product_id, p.name
) t
WHERE rnk <= 3
ORDER BY category, rnk;