Roche's commercial analytics team needs a regional view of product performance. Assume the reporting date is 2025-07-15, so the last completed quarter is 2025-04-01 through 2025-06-30.
Write a PostgreSQL query to identify the top three Roche products by revenue in each region.
Requirements
- Join sales, products, and regions using their key relationships.
- Include only sales from the last completed quarter, using an inclusive start date and exclusive end date.
- Calculate revenue as
quantity * unit_price and aggregate it by region and product.
- Rank products within each region by descending revenue with
RANK(), retaining ties at the third rank.
- Return region, product, total revenue, and rank, ordered by region, rank, and product name.
