SQL RANK() Window Function Tutorial

Understanding the RANK() Function

The RANK() function is a powerful SQL window function that assigns a rank to each row within a result set. What makes RANK() special is how it handles ties: when multiple rows have the same values, they receive the same rank, and the next rank is skipped. This creates gaps in the ranking sequence, similar to how many sports competitions handle ties.

Basic Syntax

RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ...
)

Common RANK() Interview Questions

What's the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
How would you rank videos by views and handle ties appropriately?
How can you find the third highest-viewed video in each category?
When would you choose RANK() over other ranking functions?

How RANK() Handles Ties

The key characteristic of RANK() is its approach to ties. When two or more rows have the same value in the ORDER BY column(s), they receive the same rank. The next rank assigned will skip numbers to account for the ties.

Example: Video View Rankings

Consider a scenario where we have 5 videos with the following view counts:

Video Title	Views	RANK()	Explanation
Dance Challenge	2000	1	Highest view count = rank 1
Travel Vlog	1500	2	Second highest = rank 2
Gaming Stream	1200	3	Third highest = rank 3
Music Cover	1100	4	Fourth highest = rank 4
Funny Cat	1000	5	Fifth highest = rank 5
Cooking Tips	1000	5	Same views as Funny Cat = also rank 5
Fitness Routine	900	7	Next rank is 7 (not 6) due to tie above

Notice how both "Funny Cat" and "Cooking Tips" have the same views (1000) and receive the same rank (5). The next video gets rank 7, skipping rank 6.

The RANK() Calculation Logic

RANK() follows a specific calculation method:

RANK() = 1 + (number of rows that have higher values than the current row)

This is why when there are ties, the next rank jumps forward by the number of tied values. In our example, two videos had rank 5, so the next rank is 5 + 2 = 7.

Key Components of RANK()

The OVER Clause

The OVER clause defines the window of rows that the RANK() function operates on. It specifies both the partitioning and ordering of your dataset.

PARTITION BY (Optional)

PARTITION BY divides the result set into groups. Ranking starts fresh at 1 for each new partition. This is like creating separate leaderboards for different categories.

-- Separate rankings for each user's videos
RANK() OVER (PARTITION BY user_id ORDER BY views DESC)

ORDER BY (Required)

ORDER BY determines the sequence used for ranking. This is mandatory for RANK() as it needs to know which values to compare for determining ranks.

-- Rank by view count (highest first)
RANK() OVER (ORDER BY views DESC)

Practical RANK() Examples

Basic Video Ranking

Let's use RANK() to create a leaderboard of videos by view count:

-- Rank all videos by views
SELECT 
  title,
  views,
  RANK() OVER (ORDER BY views DESC) AS view_rank
FROM 
  Videos;

This query ranks all videos based on their view count, with the highest views getting rank 1. Videos with identical view counts will receive the same rank, and the next rank will be adjusted accordingly.

Ranking Within Categories

Let's rank videos by popularity within each creator's content:

-- Rank videos by views within each creator's content
SELECT 
  u.username,
  v.title,
  v.views,
  RANK() OVER (PARTITION BY v.user_id ORDER BY v.views DESC) AS creator_rank
FROM 
  Videos v
JOIN 
  Users u ON v.user_id = u.user_id;

This query creates separate rankings for each creator's videos. The PARTITION BY clause ensures ranking starts fresh at 1 for each user_id. It's like having a separate leaderboard for each content creator.

Finding Specific Ranks

Let's find the second most popular video for each user:

TokTuk Example:

This query first ranks each user's videos by views, then filters to keep only those with rank 2. Note that if a user had multiple videos tied for the top spot, they would all get rank 1, and there might not be a rank 2 video at all for that user.

RANK() vs. Other Ranking Functions

SQL provides several ranking functions, each with slightly different behavior. Understanding these differences is crucial for choosing the right function for your specific needs.

Function	Handling of Ties	Sequence	Best For
RANK()	Same rank for ties, skips next ranks	1, 2, 2, 4, 5, ...	Competition-style rankings (e.g., Olympics)
DENSE_RANK()	Same rank for ties, no gaps	1, 2, 2, 3, 4, ...	Continuous rankings without gaps
ROW_NUMBER()	Unique number for each row, ignores ties	1, 2, 3, 4, 5, ...	Sequential numbering, pagination

Choose RANK() when you need a traditional ranking system where ties share a position and subsequent ranks reflect the number of preceding rows.

Common Use Cases for RANK()

1. Competition Leaderboards

RANK() is ideal for creating leaderboards where ties are common:

This query ranks users based on their total video views. Users with the same total views share the same rank, and the next rank skips accordingly, mimicking a real competition leaderboard.

2. Finding Top N with Ties

Use RANK() to find the top N items, including tied values:

-- Find top 3 viewed videos (including ties)
WITH RankedVideos AS (
  SELECT 
    title,
    views,
    RANK() OVER (ORDER BY views DESC) AS view_rank
  FROM 
    Videos
)
SELECT 
  title,
  views,
  view_rank
FROM 
  RankedVideos
WHERE 
  view_rank <= 3
ORDER BY 
  view_rank, title;

This query returns the top 3 ranked videos by views. If there are ties at rank 3, all tied videos are included, potentially returning more than 3 rows.

3. Analyzing Performance Tiers

RANK() can help categorize data into performance tiers:

-- Categorize videos into performance tiers based on views
SELECT 
  title,
  views,
  CASE 
    WHEN RANK() OVER (ORDER BY views DESC) <= 5 THEN 'Top Tier'
    WHEN RANK() OVER (ORDER BY views DESC) <= 20 THEN 'Mid Tier'
    ELSE 'Lower Tier'
  END AS performance_tier
FROM 
  Videos
ORDER BY 
  views DESC;

This query assigns videos to tiers based on their view rankings: top 5 are "Top Tier," next 15 are "Mid Tier," and the rest are "Lower Tier." Ties at the boundaries will share the same tier.

Performance Tips for RANK()

To ensure efficient use of RANK() in your queries:

Index Key Columns: Create indexes on columns used in ORDER BY and PARTITION BY to speed up sorting and grouping.
Filter Early: Apply WHERE clauses before ranking to reduce the dataset size.
Consider Alternatives: For simple top-N queries without tie handling, a basic ORDER BY with LIMIT might be faster.
Optimize Partitions: Use PARTITION BY judiciously, as too many partitions can increase computation time.

SQL Learning Hub

SQL RANK() Window Function