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.

Copy
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

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.

Copy
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;

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