Pathubs SQL Curriculum • Module 14

SQL DENSE_RANK() Window Function

Master gap-free ranking in SQL: understand how DENSE_RANK() handles ties without skipping numbers, compare it directly against RANK() and ROW_NUMBER(), perform partitioned group rankings, and master Top-N tier analysis.

⏱️ Estimated Time:25 Minutes

🎯 Level:Intermediate Window Analytics

📊 Track:Data Analytics & Reporting Engineering

✨ Mode:Live Ranking Lab

Introduction: The Tie Dilemma

Suppose four students receive the following test scores:

Score 100 (Amit) • Score 100 (Rahul) • Score 90 (Priya) • Score 80 (Neha)

Both Amit and Rahul earned 100 points, so they tied for 1st place. But what rank should Priya (90 points) receive?

Should Priya be in 3rd place because two students beat her? (Standard RANK() behavior with gaps).
Or should Priya be in 2nd place because 90 is the second-highest score achieved? (DENSE_RANK() behavior with NO gaps).

What Is DENSE_RANK()? (The Core Mental Model)

Definition

DENSE_RANK() assigns the identical rank to rows with equal values according to window ordering, while keeping the ranking sequence strictly consecutive without gaps (1, 1, 2, 3).

SELECT
    name,
    score,
    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS score_rank
FROM students;

Basic Example: Student Test Scores

Student Name	Score	DENSE_RANK() Output	Why?
Amit Verma	100	1	Highest score
Rahul Sharma	100	1	Tied for highest score
Priya Patel	90	2	Next distinct score (no rank skipped!)
Neha Singh	80	3	Consecutive next rank

The Core Difference: RANK() vs. DENSE_RANK()

This is the single most important conceptual comparison in SQL ranking functions:

Score	RANK() (With Gaps)	DENSE_RANK() (Gap-Free)	Key Distinction
100	1	1	Both assign 1 to the first score
100	1	1	Both assign 1 to the tied score
90	3 (Skipped 2!)	2 (Consecutive)	RANK() counts 2 rows ahead; DENSE_RANK() advances +1
80	4	3	Maintains gap vs gap-free progression

Diagram 1: RANK() (Gaps) vs DENSE_RANK() (Dense / Consecutive)

RANK()

1, 1, 2 3, 4

Skips rank numbers after ties

DENSE_RANK()

1, 1, 2, 3

Never skips rank numbers

Three-Way Matrix: ROW_NUMBER vs. RANK vs. DENSE_RANK

Comparing all three SQL ranking functions across identical test data:

Student	Score	ROW_NUMBER()	RANK()	DENSE_RANK()
Amit	100	1	1	1
Rahul	100	2 (Unique ID)	1 (Shared)	1 (Shared)
Priya	90	3	3 (Gap)	2 (No Gap)
Neha	80	4	4	3

DENSE_RANK() With PARTITION BY: Group-Level Restarts

Adding PARTITION BY department instructs SQL to restart the ranking sequence at 1 independently for each department:

SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Diagram 2: Partitioned Group Rankings

Sales Department

• Rahul ($80k) ➔ Rank 1
• Amit ($80k) ➔ Rank 1
• Neha ($70k) ➔ Rank 2

HR Department

• Priya ($70k) ➔ Rank 1
• Sara ($60k) ➔ Rank 2
• Vikram ($60k) ➔ Rank 2

How DENSE_RANK() Evaluates Multiple Duplicate Ties

DENSE_RANK() ranks by distinct values encountered in the order stream:

1000 ➔ 1st distinct value ➔ Rank 1
1000 ➔ 1st distinct value ➔ Rank 1
900  ➔ 2nd distinct value ➔ Rank 2
900  ➔ 2nd distinct value ➔ Rank 2
800  ➔ 3rd distinct value ➔ Rank 3

Top-N Analysis With DENSE_RANK()

In SQL, you cannot place a window function directly in a WHERE clause. Instead, encapsulate the ranking inside a CTE:

WITH ranked_products AS (
    SELECT
        product,
        category,
        sales,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY sales DESC
        ) AS sales_rank
    FROM products
)
SELECT *
FROM ranked_products
WHERE sales_rank <= 3;

RANK() vs. DENSE_RANK() in Business Reporting

Business Goal	Recommended Function	Reason
Podium / Tier Awards (e.g. Gold, Silver, Bronze tiers)	DENSE_RANK()	Guarantees that Silver and Bronze tiers exist even if two people tie for Gold.
Olympic-style Standings (Skip ranks after ties)	RANK()	If two runners tie for 1st, the next runner is officially 3rd.
Pagination / Unique Record Numbers	ROW_NUMBER()	Requires guaranteed unique indices without duplicates.

Real-World Data Analytics Use Cases

Executive Compensation Tiers: Finding the top 3 salary tiers within each department.
Customer Loyalty Brackets: Segmenting customers into spending tiers (Tier 1, Tier 2, Tier 3).
E-Commerce Top Sellers: Displaying the top 5 product sales positions per category without excluding tied products.

Common DENSE_RANK() Mistakes to Avoid

1. Direct WHERE Filtering

Writing WHERE DENSE_RANK() <= 3 in the same query. Window functions execute after WHERE, so a CTE is required.

2. Adding Extra ORDER BY Columns Unintentionally

Adding tie-breaker columns (e.g. ORDER BY score DESC, id ASC) breaks score ties into unique rows, defeating the purpose of DENSE_RANK.

3. Confusing PARTITION BY with GROUP BY

GROUP BY collapses multiple rows into one. PARTITION BY preserves individual rows while grouping rank calculations.

4. Expecting Exactly N Rows in Top-N

Assuming WHERE dense_rank <= 3 will return 3 rows. If 4 people tie for rank 1, all 4 are returned!

Practical Ranking Exercises

Exercise 1: Second Highest Salary Tier Per Department

WITH dept_salary_tiers AS (
    SELECT
        name, department, salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_tier
    FROM employees
)
SELECT * FROM dept_salary_tiers
WHERE salary_tier = 2;

⚡ Live Interactive SQL DENSE_RANK() Practice Lab

Test live tie handling, compare ROW_NUMBER vs RANK vs DENSE_RANK, and explore partitioned group ranking

Edit student scores in the inputs below to watch how DENSE_RANK() dynamically recalculates tied positions with zero gaps:

Student	Subject	DENSE_RANK()	Status
Amit Verma	Math	#1	Rank #1
Rahul Sharma	Math	#1	Rank #1
Priya Patel	Math	#2	Rank #2
Neha Singh	Math	#3	Rank #3
Rohan Gupta	Math	#3	Rank #3
Vikram Joshi	Math	#4	Rank #4

SQL DENSE_RANK() Best Practices

Use DENSE_RANK() for Tiered Groupings: Ideal when tied members should share a rank and subsequent tiers must remain consecutive.
Use CTEs for Top-N Filters: Wrap window functions in a CTE before applying WHERE rank <= N.
Use PARTITION BY for Sub-Group Leaderboards: Reset rankings cleanly per department, category, or region.
Avoid Unnecessary Tie-Breakers: Only add secondary ORDER BY columns if you explicitly want unique non-tied rankings.

What You Should Know Now: Checklist

✓DENSE_RANK() Definition: Assigns equal ranks to ties with no gaps in the numbering sequence (1, 1, 2, 3).
✓DENSE_RANK vs RANK: RANK creates gaps (1, 1, 3, 4); DENSE_RANK is consecutive (1, 1, 2, 3).
✓DENSE_RANK vs ROW_NUMBER: ROW_NUMBER generates unique consecutive integers (1, 2, 3, 4) ignoring ties.
✓PARTITION BY: Restarts ranking at 1 inside each independent category or department partition.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL DENSE_RANK()

Test your understanding of tie evaluation, gap-free numbering, PARTITION BY, RANK vs DENSE_RANK vs ROW_NUMBER, and Top-N query outputs.

Question 1 of 8Current Score: 0 / 0

What is the primary characteristic of DENSE_RANK() when evaluating tied rows?