Goldman Sachs reporting needs a category-level view of product revenue for the most recent 30-day reporting window. Assume the report runs on 2025-02-15, so include orders from 2025-01-17 through 2025-02-15, inclusive.
Write a PostgreSQL query that returns the top three products by revenue within each category.
Requirements
- Join Goldman Sachs product, category, and order data.
- Calculate revenue as
quantity * unit_price for orders in the 30-day window.
- Rank products independently within each category and return the top three, using product ID as a deterministic tie-breaker.
- Return results ordered by category, revenue descending, and product ID.
