Write Performant SQL at Scale

How would you write complex SQL queries that are performant at scale?

Using the supplied orders, order_items, and products tables, write one PostgreSQL query that calculates product revenue for completed orders from January 1 through June 30, 2025. Return only products ranked in the top two by revenue within their category, and exclude products with no qualifying sales.

Output

1. One row per qualifying product with category, product_id, product_name, net_revenue, units_sold, and category_rank
2. Include ties at the second rank
3. Order by category, category_rank, net_revenue descending, and product_id