Ranking Rows by Value: Top Salespeople, Best Products
You exported your Q1 sales data. You need to put a rank number next to each salesperson based on their total revenue. Employee #1, #2, #3. Sounds obvious, but as soon as two people have the same revenue, things get interesting. Are they both #3? Does the next person become #4 or #5?
SQL has three different ranking functions, and they each answer that question differently. ExploreMyData lets you pick the one that matches what you actually need.
The three ranking functions
Let's say five salespeople have revenue of $500, $400, $400, $300, $200. Here's how each function handles it:
- ROW_NUMBER: 1, 2, 3, 4, 5 - every row gets a unique number, ties are broken arbitrarily
- RANK: 1, 2, 2, 4, 5 - ties share a rank, then a gap (no rank 3)
- DENSE_RANK: 1, 2, 2, 3, 4 - ties share a rank, no gap
Which one to use? ROW_NUMBER when you need strictly unique positions (like assigning sequential IDs). RANK when ties should share a position and you want the gap to reflect how many people are ahead of you. DENSE_RANK when ties share a position and you just want consecutive numbers.
Most people want DENSE_RANK. It feels the most natural: "these two products are both #2, the next one is #3." RANK is better for competitive contexts: "you're in 4th place because three people scored higher than you, even though two of them tied."
| salesperson | revenue | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Alice | $500 | 1 | 1 | 1 |
| Bob | $400 | 2 | 2 | 2 |
| Carol | $400 | 3 | 2 | 2 |
| Dan | $300 | 4 | 4 | 3 |
| Eve | $200 | 5 | 5 | 4 |
Bob and Carol both have $400. RANK skips position 3 (gap), DENSE_RANK does not. ROW_NUMBER always assigns a unique number regardless of ties.
Adding a rank column
Open the toolbar and select Window from the Transform group.
Choose the column you want to rank by - say
revenue. Select
DENSE_RANK as the function. Set
order by to
revenue descending (highest first).
Name the result rank.
The generated SQL:
DENSE_RANK() OVER(ORDER BY revenue DESC) AS rank
Every row now has a rank number. The person with the highest revenue is rank 1. Sort the table by the rank column and you have your leaderboard.
Ranking within groups
Ranking gets more useful when you do it within groups. Instead of one global leaderboard, you want the top salesperson in each region. Or the best-selling product in each category.
Set the partition by field to your grouping column.
If you partition by region,
each region gets its own ranking starting from 1.
DENSE_RANK() OVER(PARTITION BY region ORDER BY revenue DESC) AS region_rank
The top earner in "East" is rank 1. The top earner in "West" is also rank 1. They each won their region. This is incredibly useful for finding the best performer in each group without having to filter the data multiple times.
| salesperson | region | revenue | region_rank |
|---|---|---|---|
| Alice | East | $82,000 | 1 |
| Bob | East | $61,000 | 2 |
| Carol | East | $45,000 | 3 |
| Dan | West | $94,000 | 1 |
| Eve | West | $71,000 | 2 |
| Frank | West | $38,000 | 3 |
SQL: DENSE_RANK() OVER(PARTITION BY region ORDER BY revenue DESC) AS region_rank. Each region restarts at rank 1.
Getting the top N per group
Once you have a rank column, filtering to the top 3 per group is straightforward. Add a
Filter step where
region_rank <= 3.
Now you have a table showing only the top 3 salespeople in each region.
This is a pattern that comes up constantly in reporting: top 5 products per category, top 10 customers per segment, worst-performing stores per territory. The rank-then-filter approach handles all of them.
Ranking by multiple criteria
What if revenue is the primary ranking, but you want to break ties by number of deals? You can do this with Add Column using a custom expression:
DENSE_RANK() OVER(ORDER BY revenue DESC, num_deals DESC) AS rank
When two people have the same revenue, the one with more deals ranks higher. This is common in sales contexts where volume matters as a tiebreaker.
Percentile ranking
Sometimes you don't want an absolute rank - you want to know where a value falls relative to the whole distribution. "This product is in the 90th percentile for revenue."
Use Add Column with the PERCENT_RANK()
function:
ROUND(PERCENT_RANK() OVER(ORDER BY revenue) * 100, 1) AS percentile
This returns a number between 0 and 100. A percentile of 95 means this row's revenue is higher than 95% of all other rows. Useful for identifying outliers and segmenting data into tiers (top 10%, middle 50%, bottom 40%).
A note on pre-aggregated vs raw data
If your data is one row per transaction, you probably want to aggregate first (Group By salesperson, SUM revenue) before ranking. Ranking individual transactions doesn't make much sense. If your data is already aggregated (one row per salesperson with total revenue), you can rank directly.
ExploreMyData's pipeline makes this easy: add a Group By step first, then a Window step for ranking. Each step builds on the previous result.