order_items(order_id, product_id, category, revenue, ordered_at)

For 2026, return the top two products by total revenue within each category, including every product tied at second place.

Return category, product_id, revenue and rank, ordered by category then rank then product.

Because ties at second are kept, a category can return more than two rows. That rules out ROW_NUMBER, which would arbitrarily pick one of them.

Tables

order_items

order_id  product_id  category  revenue  ordered_at
--------  ----------  --------  -------  ----------
1         1           A         600.00   2026-01-01
2         1           A         400.00   2026-02-01
3         2           A         500.00   2026-01-01
4         3           A         500.00   2026-01-01
5         4           A         100.00   2026-01-01
6         5           B         300.00   2026-01-01
7         6           B         200.00   2026-01-01
8         1           A         9999.00  2025-12-31

Expected result

category  product_id  revenue  rank
--------  ----------  -------  ----
A         1           1000.00  1
A         2           500.00   2
A         3           500.00   2
B         5           300.00   1
B         6           200.00   2