Write a SQL query using a window function to retrieve the top 3 customers per region by revenue, handling ties with RANK().

Question

Assisting AI · Accepted Answer

The solution uses a Common Table Expression (CTE) to compute total revenue per customer per region, then applies RANK() to order those totals within each region. The final SELECT filters for ranks 1 through 3. Example:

```sql
WITH revenue AS (
  SELECT
    r.region_name,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) 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;
```

This query runs in O(n log n) time due to the window function's internal sort and uses O(n) space for the CTE.

Write a SQL query using a window function to retrieve the top 3 customers per region by revenue, handling ties with RANK().

💡 Model Answer

🎤 Get questions like this answered in real-time