Ranking - ROW_NUMBER(), RANK(), DENSE_RANK()

🎬 Scenario: Ranking Meta Posts by Engagement#

Your manager at Meta, Jordan, wants to see which posts have received the most likes and how they rank against others.

Can you generate a report ranking posts based on likes while handling ties correctly?

Your task is to use window functions (ROW_NUMBER(), RANK(), and DENSE_RANK()) to generate a ranking report. Use the meta_posts table to solve the question.

1. Understanding Window Functions for Ranking#

Window functions allow you to assign ranks while keeping the full dataset intact.

Syntax for Ranking Functions

SELECT column1, column2,  
       function() OVER (PARTITION BY column ORDER BY column) AS rank  
FROM table_name;

• PARTITION BY groups rankings within each category.
• ORDER BY determines ranking criteria.

2. Using ROW_NUMBER() for Unique Ranking#

Jordan wants to assign a unique rank to each post based on the number of likes.

Example Query: Assigning Unique Ranks

SELECT post_id, user_id, likes,  
       ROW_NUMBER() OVER (ORDER BY likes DESC) AS rank  
FROM meta_posts;

What’s Happening?

• ROW_NUMBER() assigns a unique rank to each post.
• If two posts have the same likes, their ranks still differ.

3. Using RANK() to Handle Ties#

Jordan now wants to assign the same rank to tied values while keeping gaps in ranking.

Example Query: Handling Ties with RANK()

SELECT post_id, user_id, likes,  
       RANK() OVER (ORDER BY likes DESC) AS rank  
FROM meta_posts;

What’s Happening?

• RANK() assigns the same rank to tied values.
• Gaps appear in the ranking (i.e., after rank 3, it jumps to rank 5).

4. Using DENSE_RANK() to Handle Ties Without Gaps#

Jordan prefers a ranking without gaps when ties occur.

Example Query: Handling Ties with DENSE_RANK()

SELECT post_id, user_id, likes,  
       DENSE_RANK() OVER (ORDER BY likes DESC) AS rank  
FROM meta_posts;

What’s Happening?

• DENSE_RANK() assigns the same rank to ties but avoids gaps.

✍️ SQL Exercises#

Exercise 1: Assigning Unique Ranks to Posts#

Jordan wants to assign unique ranks to each post based on the number of likes.

Filter condition:

• Retrieve post ID, user ID, likes, and rank.
• Assign unique ranks using ROW_NUMBER().
• Sort by likes in descending order.

Write an SQL query to return the requested data.

Exercise 2: Ranking Posts While Handling Ties#

Jordan also wants to assign the same rank to posts with equal likes but without gaps.

Filter condition:

• Retrieve post ID, user ID, likes, and rank.
• Assign rank using DENSE_RANK().
• Sort by likes in descending order.

Write an SQL query to return the requested data.