Use the RANK() window function partitioned by region to retrieve the top 3 customers per region by revenue.

To get the top three customers in each region by revenue, first aggregate revenue per customer per region, then rank those aggregates within each region. A common pattern is to use a CTE or subquery to compute the totals, followed by a window function that partitions by region and orders by revenue descending. Finally, filter on the rank. Example:

WITH revenue AS (
  SELECT
    r.region_name,
    c.customer_name,
    SUM(oi.unit_price * oi.quantity) AS total_revenue
  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_name, c.customer_name
)
SELECT
  region_name,
  customer_name,
  total_revenue,
  RANK() OVER (PARTITION BY region_name ORDER BY total_revenue DESC) AS rank_in_region
FROM revenue
WHERE rank_in_region <= 3;

The query runs in O(n log n) time due to the sort performed by the window function, and uses O(n) additional space for the intermediate result. Using RANK() instead of ROW_NUMBER() preserves ties, so customers with identical revenue share the same rank and may push subsequent ranks further down, which matches the requirement to include all tied customers in the top three.