Example 5: Top N Sales by Region

This SQL query ranks salespersons within each region based on their sales amount in descending order and selects the top three salespersons for each region. It returns the region, salesperson, sales amount, and their rank within the region.

SELECT region, 
       sales_person, 
       sales_amount, 
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS row_num
FROM regional_sales
WHERE row_num <= 3
ORDER BY region, row_num;

Query Breakdown:

1. SELECT Clause:
   • region: Retrieves the region of the sales transaction.
   • sales_person: Retrieves the name or ID of the salesperson.
   • sales_amount: Retrieves the amount of sales made by the salesperson.
   • ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS row_num: This is the window function calculating the row number for each salesperson within their region, ordered by sales amount in descending order.
2. Window Function Explanation:
   • ROW_NUMBER(): Assigns a unique sequential integer to rows within each partition.
   • OVER: Defines the window of rows for the function.
     • PARTITION BY region: Divides the result set into partitions based on region. Each region is treated as a separate group.
     • ORDER BY sales_amount DESC: Orders the rows within each partition by sales_amount in descending order, assigning higher row numbers to higher sales amounts.
3. FROM Clause:
   • FROM regional_sales: Specifies the table from which to retrieve the data.
4. WHERE Clause:
   • WHERE row_num <= 3: Filters the result set to include only the top three salespersons per region.
5. ORDER BY Clause:
   • ORDER BY region, row_num: Orders the final result set first by region and then by row_num within each region.

Detailed Step-by-Step Execution

Initial State: Assume the regional_sales table has the following data:

Processing Each Region:

• Region: North:
  • Alice: Sales Amount = 30000, Row Number = 1
  • Charlie: Sales Amount = 28000, Row Number = 2
  • Bob: Sales Amount = 25000, Row Number = 3
  • David: Sales Amount = 22000 (Excluded due to row number > 3)
• Region: South:
  • Grace: Sales Amount = 40000, Row Number = 1
  • Eva: Sales Amount = 35000, Row Number = 2
  • Frank: Sales Amount = 30000, Row Number = 3
  • Helen: Sales Amount = 25000 (Excluded due to row number > 3)

Final Output: