RANK and DENSE_RANK

The RANK() and DENSE_RANK() functions assign a ranking number to rows within a group.

These functions are commonly used when ordering data by progress, score, price, or other metrics.

They work as window functions using the OVER() clause.

Syntax

RANK and DENSE_RANK syntax

SELECT user_name,
       RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS dense_rank
FROM course_progress;

• PARTITION BY groups rows by course
• ORDER BY defines how rankings are assigned

Example: Ranking Course Completion

We have user progress for two courses, SQL Basics and Python Intro.

Each user has a completion_rate from 0 to 100, and we want to rank them within each course by that value.

We can use RANK() and DENSE_RANK() to see how users compare to others in the same course.

Ranking users by completion rate

SELECT user_name,
       course_name,
       completion_rate,
       RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY course_name ORDER BY completion_rate DESC) AS dense_rank
FROM course_progress;

Output:

user_name	course_name	completion_rate	rank	dense_rank
Heidi	Python Intro	92	1	1
Frank	Python Intro	90	2	2
Grace	Python Intro	90	2	2
Eve	Python Intro	85	4	3
Bob	SQL Basics	95	1	1
Charlie	SQL Basics	95	1	1
Alice	SQL Basics	92	3	2
Diana	SQL Basics	90	4	3

• RANK() skips numbers after ties (e.g. 1 → 1 → 3)
• DENSE_RANK() does not skip (e.g. 1 → 1 → 2)

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In