SQL Ranking - ROW_NUMBER(), RANK(), DENSE

Lesson Objectives

By the end of this lesson, you will:

Learn how to use ROW_NUMBER() to assign unique ranks
Use RANK() and DENSE_RANK() to handle ties
Write SQL queries to analyze Meta post engagement using ranking functions

🎬 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.

post_id	user_id	post_content	post_date	likes	shares	post_status
1	1	“Had a great day at the beach!”	“2024-01-10T14:30:00”	120	45	“Public”
2	2	“Launching our new product!”	“2024-01-12T09:20:00”	250	90	“Friends Only”
3	3	“Just finished a 10K run!”	“2024-01-15T18:10:00”	80	30	“Public”
4	4	“Hosting a webinar next week.”	“2024-01-18T20:45:00”	50	25	“Private”
5	5	“Traveling to Japan soon!”	“2024-01-20T10:05:00”	180	75	“Public”
6	1	“Excited for the weekend!”	“2024-01-25T16:15:00”	95	40	“Friends Only”
7	2	“Need recommendations for a new laptop.”	“2024-01-27T12:40:00”	70	20	“Public”
8	3	“Learning SQL window functions!”	“2024-01-29T08:55:00”	110	50	“Public”
9	4	“Working on a new AI model.”	“2024-02-01T14:00:00”	200	100	“Friends Only”
10	5	“Book recommendations?”	“2024-02-05T11:30:00”	65	15	“Public”

1. Understanding Window Functions for Ranking

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

Function	What It Does	Handles Ties?	Gaps in Rank?
ROW_NUMBER()	Assigns a unique rank to each row	❌ No	✅ Yes
RANK()	Assigns the same rank to tied values	✅ Yes	✅ Yes
DENSE_RANK()	Assigns the same rank to ties but avoids gaps	✅ Yes	❌ No

Syntax for Ranking Functions

1SELECT column1, column2,  
2       function() OVER (PARTITION BY column ORDER BY column) AS rank  
3FROM 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

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

post_id	user_id	likes	rank
2	2	250	1
9	4	200	2
5	5	180	3
1	1	120	4
8	3	110	5
6	1	95	6
3	3	80	7
7	2	70	8
10	5	65	9
4	4	50	10

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()

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

post_id	user_id	likes	rank
2	2	250	1
9	4	200	2
5	5	180	3
1	1	120	4
8	3	110	5
6	1	95	6
3	3	80	7
7	2	70	8
10	5	65	9
4	4	50	10

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()

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

post_id	user_id	likes	rank
2	2	250	1
9	4	200	2
5	5	180	3
1	1	120	4
8	3	110	5
6	1	95	6
3	3	80	7
7	2	70	8
10	5	65	9
4	4	50	10

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.

post_id	user_id	likes	rank
2	2	250	1
9	4	200	2
5	5	180	3
1	1	120	4
8	3	110	5
6	1	95	6
3	3	80	7
7	2	70	8
10	5	65	9
4	4	50	10

Write an SQL query to return the requested data.

Upgrade to Pro

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.

post_id	user_id	likes	rank
2	2	250	1
9	4	200	2
5	5	180	3
1	1	120	4
8	3	110	5
6	1	95	6
3	3	80	7
7	2	70	8
10	5	65	9
4	4	50	10

Write an SQL query to return the requested data.

Upgrade to Pro

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

Dan Lee

Lesson Objectives

🎬 Scenario: Ranking Meta Posts by Engagement

1. Understanding Window Functions for Ranking

Syntax for Ranking Functions

2. Using ROW_NUMBER() for Unique Ranking

Example Query: Assigning Unique Ranks

3. Using RANK() to Handle Ties

Example Query: Handling Ties with RANK()

4. Using DENSE_RANK() to Handle Ties Without Gaps

Example Query: Handling Ties with DENSE_RANK()

✍️ SQL Exercises

Exercise 1: Assigning Unique Ranks to Posts

Exercise 2: Ranking Posts While Handling Ties