SQL Window Functions: Ranking Sales by Region in One Query

Window functions in SQL offer a powerful way to perform calculations across sets of rows related to the current query row—without collapsing them into a single result. One compelling use case is ranking data within partitions, such as ranking salespeople within their respective regions. In this blog post, we’ll explore how to use ROW_NUMBER(), RANK(), and DENSE_RANK() to achieve this in a single query.

1. Setting Up the Scenario: Ranking Salespeople by Region

Assume you have a table named sales with the following structure:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    region VARCHAR(50),
    total_sales DECIMAL(10, 2)
);

And sample data might look like:

INSERT INTO sales (name, region, total_sales) VALUES
('Alice', 'West', 95000.00),
('Bob', 'West', 87000.00),
('Carol', 'West', 87000.00),
('Dan', 'East', 91000.00),
('Eve', 'East', 99000.00),
('Frank', 'East', 91000.00);

Your goal is to rank the salespeople within each region based on total sales.

2. Introducing ROW_NUMBER(): Unique Ordered Ranking

ROW_NUMBER() assigns a unique number to each row within a partition. No ties are allowed—if values are identical, one will still rank above the other arbitrarily (or by a secondary column).

SELECT 
    name,
    region,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) AS row_num
FROM sales;

How it works: The PARTITION BY clause creates subgroupings by region, and ORDER BY total_sales DESC determines the ranking order within each group. Because ties are broken arbitrarily, this function is best when you want a strict ranking regardless of tie values.

3. Using RANK(): Ranking with Gaps for Ties

RANK() is similar to ROW_NUMBER() but it introduces gaps in the ranking sequence when there are ties. If two people tie for first, the next will be ranked third.

SELECT 
    name,
    region,
    total_sales,
    RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS sales_rank
FROM sales;

Why use RANK(): This is useful in leaderboards where tied scores deserve equal standing, and it’s important to skip ranks appropriately.

Example: If Eve and Dan both had 99k, they’d get rank 1, and the next rank would be 3.

4. Using DENSE_RANK(): Flexible Ties Without Gaps

DENSE_RANK() behaves like RANK(), but without gaps. Tied values receive the same rank, and the next rank continues consecutively.

SELECT 
    name,
    region,
    total_sales,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS dense_rank
FROM sales;

Why use DENSE_RANK(): Ideal for compact rankings. For instance, in prize distributions where two second-place finishers mean the next is third rather than fourth.

5. Comparing Results All Together

Let’s combine all three window functions in a single query to highlight their behavior side-by-side:

SELECT 
    name,
    region,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) AS row_number,
    RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS dense_rank
FROM sales
ORDER BY region, total_sales DESC;

This unified view is powerful for analytics dashboards, reports, or performance reviews where multiple ways of viewing rank are valuable.

6. Performance Considerations and Tips

Use appropriate indexes—especially if you have large datasets. Often, indexing on region and total_sales improves window function performance.
If you don’t need every row, use ROW_NUMBER() in a derived table or CTE with a filter: e.g., top 1 per region.

Example:

WITH ranked_sales AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) AS rn
  FROM sales
)
SELECT name, region, total_sales
FROM ranked_sales
WHERE rn = 1;

This query returns the top-selling salesperson from each region.

Conclusion

SQL window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() allow you to analyze and rank data elegantly and efficiently. They’re indispensable for analytical queries—especially in business scenarios like sales performance tracking. By understanding their differences and use cases, you’ll write smarter and more powerful SQL queries.

Now that you know how to rank salespeople by region, try applying this technique to customer spending, order volumes, or inventory turnovers. Happy querying!

Useful links: