How does the RANK() window function work when partitioned by region_name to rank customers by total revenue, and how would you write such a query?

The RANK() function assigns a rank to each row within a partition based on the order of a specified expression. When you partition by region_name and order by total revenue descending, each region gets its own ranking sequence. Ties receive the same rank, and the next rank is skipped accordingly.

Example query:

SELECT
  region_name,
  customer_id,
  SUM(unit_price * quantity) AS total_revenue,
  RANK() OVER (PARTITION BY region_name ORDER BY SUM(unit_price * quantity) DESC) AS revenue_rank
FROM sales
WHERE status = 'completed'
GROUP BY region_name, customer_id
ORDER BY region_name, revenue_rank;

Explanation:

1. SUM(unit_price * quantity) aggregates revenue per customer.
2. The WHERE clause filters only completed orders.
3. PARTITION BY region_name restarts the ranking for each region.
4. ORDER BY ... DESC ranks higher revenue first.
5. RANK() outputs the rank; customers with identical revenue share the same rank.

This pattern is useful for generating leaderboards or identifying top performers within each geographic segment.