Use the RANK() window function over a filtered and aggregated dataset to handle ties and retrieve the top 3 customers per region by revenue.

Question

Assisting AI · Accepted Answer

The approach is identical to the first question: aggregate revenue per customer per region, then apply RANK() to assign a rank within each region. The key difference is that RANK() will give the same rank to customers with equal revenue, ensuring that ties are handled correctly. A concise query:

```sql
SELECT region_name, customer_name, total_revenue, rank_in_region
FROM (
  SELECT
    r.region_name,
    c.customer_name,
    SUM(oi.unit_price * oi.quantity) AS total_revenue,
    RANK() OVER (PARTITION BY r.region_id ORDER BY SUM(oi.unit_price * oi.quantity) DESC) AS rank_in_region
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN regions r ON c.region_id = r.region_id
  WHERE o.status = 'completed'
  GROUP BY r.region_id, r.region_name, c.customer_name
) t
WHERE rank_in_region <= 3;
```

Complexity remains O(n log n) for sorting within each partition, and the query is efficient for typical relational databases.

Use the RANK() window function over a filtered and aggregated dataset to handle ties and retrieve the top 3 customers per region by revenue.

💡 Model Answer

🎤 Get questions like this answered in real-time