SAP Labs wants a regional revenue view for products sold through its enterprise portfolio. Write a PostgreSQL query that identifies the five highest-revenue products within each sales region.
Requirements
- Join orders to customers, regions, order items, and products.
- Include only orders with
status = 'COMPLETED' and positive quantities.
- Calculate revenue as
quantity * COALESCE(unit_price, list_price) and aggregate it by region and product.
- Rank products independently within each region and return the top five, using
product_id as the deterministic tie-breaker.
- Return regions in ascending order, with revenue descending within each region.
Representative Data
Completed orders contain multiple product lines, while cancelled and pending orders must be excluded. Some products have no qualifying sales, and one completed line has a NULL transaction price.
