Top N per group

The 3 best-selling products in every category with ROW_NUMBER().

sql advanced #window-functions#ranking#reports
Open in playground
sql-top-n-per-group.sql
SELECT category, product, revenue
FROM (
    SELECT c.name AS category,
           p.name AS product,
           SUM(oi.quantity * oi.unit_price) AS revenue,
           ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank
    FROM order_items oi
    JOIN products p   ON p.id = oi.product_id
    JOIN categories c ON c.id = p.category_id
    GROUP BY c.id, c.name, p.name
) ranked
WHERE rank <= 3
ORDER BY category, revenue DESC;