Lesson 17: ROW_NUMBER vs RANK vs DENSE_RANK — Which One Breaks Your Logic?

8. Introduction: Why This Matters to Every Developer

Welcome back to Fun with Learning Technology. Today, we are tackling a topic that trips up even senior engineers when they first dive deep into database analytics: SQL Window Functions. Specifically, the holy trinity of ranking: ROW_NUMBER, RANK, and DENSE_RANK.

If you have ever written a query where two employees tie for "first place," but your report shows them as 1st and 2nd when they should be 1st and 1st (with no one at 2nd), you have encountered the nuances of these functions. Interviewers love testing this because it reveals whether a candidate understands data logic versus just syntax memorization. They want to know if you understand that "ranking" implies grouping, while "numbering" implies sequencing.

This lesson is designed for developers ready to move beyond basic aggregations (like simple GROUP BY sums) and into complex analytical reporting where context matters. We will not just show you the code; we will build the intuition behind why a gap in numbers matters, when to preserve raw data integrity, and how to choose the right tool for your specific analytical needs.

9. Problem Overview: The Lineup Analogy

Imagine you are organizing a line of people by height. You want to assign each person a "position" or a "score." However, what happens if two people are exactly the same height? Do they both get position 1? Or does one get 1 and the next get 2, pushing the rest down? Or do we skip the number 2 entirely so that after the tied pair of #1s, the next person is immediately #3?

In SQL terms, we have three distinct ways to answer this question without deleting rows or collapsing data into a single group. These tools allow us to "look at neighbors" (the window) while staying on our specific plot of land (the current row).

• Scenario A: We just need a unique ID for every line item, regardless of value equality. (Strict Sequencing).
• Scenario B: We are running a competition. Ties share the spot, and subsequent spots are skipped to reflect the gap in performance. (Competition Ranking).
• Scenario C: We want to track trends across distinct levels without worrying about the gaps created by ties. (Dense Packing).

Our goal today is to master the syntax (OVER, ORDER BY) and the logic of each approach so you can handle complex queries like "Find the Top 3 Salespeople in Each Region" correctly, even when sales figures are identical.

10. Example: Setting the Stage

Let's create a concrete dataset to visualize these differences. Imagine a company with five employees sorted by their salary.

ID	Name	Salary
101	Alice	90,000
102	Bob	90,000
103	Charlie	85,000
104	Diana	80,000
105	Eve	75,000

Notice that Alice and Bob have identical salaries. This is our critical pivot point where the three functions will diverge.

11. Intuition: Discovering the Solution

Before writing a single line of code, an experienced engineer thinks about the output shape required for their report.

The Core Question: "If two items are equal in value, do I want them to have a unique sequential number (1, 2, 3...) or should they share a rank with a gap (1, 1, 3...), or do I want no gaps at all (1, 1, 2...)"

1. The Strict List (ROW_NUMBER): Think of this like handing out numbered tickets at a concert. Everyone gets a ticket from 1 to 5. It doesn't matter if two people have the same height; the line must move forward so that no two rows ever have the same id. This is purely for ordering and pagination.
2. The Tournament Leaderboard (RANK): Think of an Olympic medal table. If two athletes tie for first place with a time of 9.8 seconds, they both get Gold (Rank 1). The next athlete cannot be "Second"; they must be the third-best performer overall. The system honors the gap in performance.
3. The Trend Chart (DENSE_RANK): Think of plotting data points on a graph. If we have two data points at level 90k, the next distinct level should naturally be labeled "Level 2". We don't care about the empty space between level 1 and level 3; we just want adjacent numbers to calculate deltas or percentiles easily.

The Technical Hook: All three functions belong to the family of Window Functions. The magic happens in the OVER clause, specifically within the PARTITION BY (to group) and ORDER BY (to sort). Without these, SQL collapses rows into aggregate summaries, which destroys the row-level data we need for these rankings.

12. Brute Force Solution: The Naive Approach

If you have never used window functions, what would your first attempt look like? You might try to use a standard SELECT COUNT(*) subquery or self-join to manually count how many people are "better" than the current person.

The Algorithm: For every row in the table, iterate through all other rows. If Row B has a higher salary than Row A, increment a counter for Row A. Then order by that counter.

Why it fails as a primary solution: While this logic works conceptually (counting "better" neighbors), doing it via self-joins for every single row results in an $O(N^2)$ complexity. If your table has 1 million rows, the query explodes instantly.

Furthermore, handling ties with manual counting requires complex CASE statements inside the count logic to decide whether to include a tied value in the "better than" set. It quickly becomes unreadable and error-prone.

Complexity:

• Time Complexity: $O(N^2)$ - Exponential degradation on large datasets.
• Space Complexity: $O(N)$ - Requires materializing intermediate result sets for joins.

We will never write this in production, but understanding it helps us appreciate the elegance of Window Functions, which solve this in linear time $O(N)$.

13. Optimal Solution: The Window Function Approach

Here is how a senior engineer approaches this problem using standard SQL features introduced in SQL-99 and standardized across PostgreSQL, MySQL, SQL Server, and Snowflake.

The optimal solution relies on the syntax structure of the OVER clause. We select our original columns (to preserve data) and add a new column calculated by one of our three functions.

Step-by-Step Logic:

1. Select Original Data: Ensure we keep ID, Name, Salary. Window functions do not aggregate; they add columns to the existing rows.
2. Apply the Function: Choose ROW_NUMBER(), RANK(), or DENSE_RANK().
3. Define the Scope (OVER): This is the most critical step. We must tell SQL: "Look at this window of rows."
4. Partition (Optional): If ranking per department, use PARTITION BY department_id. If ranking globally, omit this.
5. Order: Use ORDER BY salary DESC so the highest earner is at the top of the rank list.

This approach runs in $O(N)$ time because the database engine can process the window frame in a single pass through the sorted data.

14. Python Code: Production-Ready Implementation

While the logic belongs in SQL, we often prototype or validate these concepts in Python using libraries like pandas before pushing to production databases. However, for this tutorial, we will focus on the raw SQL logic which is universal, and provide a Python script that simulates the result set for verification.

Below is a complete solution demonstrating how these functions behave logically.

-- Scenario 1: ROW_NUMBER - Strict Sequencing
SELECT 
    id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn_number
FROM employees;

-- Scenario 2: RANK - Competition Leaderboard (Skips Gaps)
SELECT 
    id,
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rk_rank
FROM employees;

-- Scenario 3: DENSE_RANK - Adjacent Trends (No Gaps)
SELECT 
    id,
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr_dense
FROM employees;

Note: If you are using Python with Pandas to simulate this analysis before database deployment:

import pandas as pd
import numpy as np

# Simulating the Employee DataFrame
data = {
    'id': [101, 102, 103, 104, 105],
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'salary': [90000, 90000, 85000, 80000, 75000]
}

df = pd.DataFrame(data)

# 1. ROW_NUMBER (Strict Order)
# Even with ties, we assign a unique index
df['row_number'] = df.groupby([pd.NA]).cumcount() + 1 

# 2. Simulating RANK (Grouping ties, skipping subsequent numbers)
# We calculate the cumulative count of strictly greater values
df['rank'] = df['salary'].rank(method='min') # 'min' is equivalent to SQL RANK in Pandas

# 3. Simulating DENSE_RANK (Grouping ties, no gaps)
# Equivalent to rank method='dense' in Pandas
df['dense_rank'] = df['salary'].rank(method='dense')

15. Code Walkthrough: Understanding the Magic

Let's break down the SQL syntax used above.

• ROW_NUMBER() OVER (...): This function generates a unique integer for every row in the window. The parentheses are empty because ROW_NUMBER does not care about values; it just counts rows 1, 2, 3...
• RANK() OVER (ORDER BY salary DESC): This function calculates the rank based on value. If two rows have the same salary, they share the same rank number. Crucially, when the next row is processed, its rank will be $N + (\text{number of tied rows})$. In our example, if 2 people are Rank 1, the next person becomes Rank 3 (skipping 2).
• DENSE_RANK() OVER (...): Similar to RANK, it groups ties. However, unlike RANK, it does not skip integers. If 2 people are Rank 1, the next distinct value gets Rank 2 immediately.
• ORDER BY salary DESC: This is mandatory inside the OVER clause. Window functions need to know how to traverse the data. Sorting descending puts the "best" values first. Without this, the ranking would be random or based on physical storage order (which is bad practice).

16. Dry Run: Tracing Execution Logic

Let's manually trace the execution for our dataset where Alice and Bob tie at $90,000.

The Input:

Row 1: Alice ($90k)
Row 2: Bob   ($90k)
Row 3: Charlie ($85k)
...

Trace for ROW_NUMBER:

1. Sort by salary: [Alice, Bob, Charlie...]
2. Counter = 1 -> Assign to Alice. Output: rn = 1.
3. Counter = 2 -> Assign to Bob. Output: rn = 2.
4. Observation: Even though salaries are equal, the numbers are unique. This is perfect for pagination (e.g., "Show me rows 1-10").

Trace for RANK:

1. Sort by salary: [Alice, Bob, Charlie...]
2. Algorithm checks: "Is Alice better than anyone before her?" No. Rank = 1.
3. Algorithm checks Bob: "Is Bob better than Alice?" No (Equal). Rank = 1.
4. Algorithm checks Charlie ($85k): "How many people are better or equal to me?"
   • People better than Charlie: Alice, Bob (2 people).
   • Current Index: 3.
   • Formula: $3 - (\text{count of ties}) = 3 - 2 = 1$? No, simpler logic: Count strictly better + current position logic.
   • Actually, standard Rank logic: The rank is the count of distinct values smaller than the current value plus one.
   • Let's stick to the gap rule: Alice/Bob are Rank 1. The next slot must be Rank 3 because Rank 2 does not exist in the "better than" set.
5. Output: Alice=1, Bob=1, Charlie=3.
6. Observation: There is a "hole" at number 2.

Trace for DENSE_RANK:

1. Sort by salary: [Alice, Bob, Charlie...]
2. Alice ($90k) is the first distinct value. Rank = 1.
3. Bob ($90k) is tied. Rank = 1.
4. Charlie ($85k) is the second distinct value. Since we count distinct values, his rank is 2 immediately.
5. Output: Alice=1, Bob=1, Charlie=2.
6. Observation: No holes. The sequence is dense (1, 1, 2).

17. Complexity Analysis

Why are these functions efficient?

• Time Complexity ($O(N \log N)$ or $O(N)$ depending on implementation): Sorting the data takes $O(N \log N)$. Window functions generally perform a linear pass over the sorted partitioned data to calculate offsets, adding $O(N)$. Most database optimizers can sort and compute windows in a single optimized step.
• Space Complexity ($O(N)$): The database needs to hold the sorted state of the window frame in memory (or disk spill) for each partition. In high-concurrency scenarios, this can increase memory pressure if partitions are large, but it is standard practice and far cheaper than self-joins.

18. Alternative Solutions: The Self-Join Method

Before Window Functions existed, engineers used a "self-join" approach to rank rows.

The Logic: For each row t, count how many other rows s have salary > t.salary.

SELECT 
    t.id, 
    t.name, 
    (SELECT COUNT(*) FROM employees s WHERE s.salary > t.salary) + 1 as manual_rank
FROM employees t;

Comparison:

• Readability: The Window Function is readable in one line. The self-join requires subqueries and can be hard to debug.
• Performance: The self-join approach executes a join for every single row. If the table has $N$ rows, it performs roughly $N^2$ comparisons. This causes queries to time out on datasets larger than a few thousand rows. Window functions are vastly superior for performance and scalability.

19. Edge Cases: Handling the Tricky Inputs

As a senior engineer, you must anticipate what breaks your logic. Here are the critical edge cases:

1. Empty Input: If the table is empty, all three functions return an empty set. No errors occur, but ensure your application handles NULL results gracefully.
2. All Ties (Equal Salaries):
   • ROW_NUMBER: Returns 1, 2, 3... (Unique IDs).
   • RANK: Returns 1, 1, 1... (Everyone is Rank 1). The next partition would be skipped if there was data.
   • DENSE_RANK: Returns 1, 1, 1... (Everyone is Rank 1).
   • Implication: If everyone earns the same, RANK and DENSE_RANK become identical.
3. Null Values in Salary: SQL ORDER BY often places NULLs at the end. Unless you specify ORDER BY salary DESC NULLS FIRST, ties involving nulls might be ranked lower than expected depending on the DBMS (PostgreSQL vs MySQL