Top two revenue positions per category

A category head wants the top two products by total revenue in every category. If two products tie, they share the same position and the next position is not skipped, so a tie can return more than two rows for a category.

From product_sales, return category, product_name, total_revenue and rnk for products ranked 1 or 2 within their category. Order by category, rnk, then product_name.

1. Step 1: Sum revenue per product first, because each product has several sale rows.
2. Step 2: `DENSE_RANK()` gives tied products the same rank and does not skip the next one. `RANK()` would give Power Bank rank 3 in Electronics and drop it; `ROW_NUMBER()` would arbitrarily drop one of the tied products.
3. Step 3: Filter `rnk <= 2` in the outer query because window results cannot be filtered in the same `SELECT`.

WITH product_totals AS (
    SELECT category, product_name, SUM(revenue) AS total_revenue
    FROM product_sales
    GROUP BY category, product_name
),
ranked AS (
    SELECT category, product_name, total_revenue,
           DENSE_RANK() OVER (
               PARTITION BY category
               ORDER BY total_revenue DESC
           ) AS rnk
    FROM product_totals
)
SELECT category, product_name, total_revenue, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk, product_name;