RANK and DENSE_RANK

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

These functions are commonly used to order data by progress, score, price, or other metrics.

They are window functions that operate with 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 within each course
• ORDER BY defines the order used to assign rankings

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 based on that value.

We can use RANK() and DENSE_RANK() to compare each user 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() assigns the same rank to ties but leaves gaps afterward (e.g., 1 → 1 → 3).
• DENSE_RANK() also assigns the same rank to ties but without gaps (e.g., 1 → 1 → 2).

Use RANK() if the gap matters (like in competitions), and DENSE_RANK() if you want continuous rankings for reporting or analysis.