SQL Ranking Window Functions With Examples

Ranking functions are incredibly useful in SQL when you want to rank rows based on specific criteria. Let's dive into the world of ranking and learn how these functions work!

What is a Ranking Window Function?

In SQL, ranking functions help us assign ranks to rows based on partitioning and order expressions. Think of these ranks as numbers that indicate the position of a row within a specific "window" of data. A window is just a set of rows that meet certain conditions - hence the name "window function"! πŸ˜‰

Before we delve into the differences, let's look at the generic syntax of ranking functions:


  1. ROW_NUMBER(): This function assigns a unique sequential number to each row within a window. It's like numbering the rows in order.

  2. RANK(): The function handles tied values by assigning the same rank to them. However, it may skip subsequent ranks, leaving gaps in the sequence.

  3. DENSE_RANK(): Similar to , also handles tied values by assigning the same rank. However, it does not skip ranks, resulting in no gaps in the sequence.

SQL Window Function Example: Ranking Concert Revenue

Let's use a dataset containing information about artists and their concert revenue and see how the ranking functions differ.


Column NameTypeDescription
artist_idintegerA unique identifier for each artist or band performing in the concert.
artist_namevarchar(100)The name of the artist or band performing in the concert.
genrevarchar(50)The music genre associated with the concert.
concert_revenueintegerThe total revenue generated from the concert.
year_of_formationintegerThe year that the artist or band was formed.
countryvarchar(50)The country of origin or residence of the artist or band.
number_of_membersintegerThe number of members in the band.
album_releasedintegerThe total number of albums released by the artist or band.
labelvarchar(100)The record label or music company associated with the artist or band.


103Taylor SwiftPop7000002004United States19Republic Records
104BTSK-Pop8000002013South Korea77Big Hit Music
105AdelePop6000002006United Kingdom13Columbia Records
109BlackpinkK-Pop4500002016South Korea45YG Entertainment
110Maroon 5Pop5500001994United States57Interscope Records

Window Function Ranking with ORDER BY

Write a query to display the list of artists sorted in descending order of their concert revenue.


Ariana Grande720000333
Bruno Mars700000444
Taylor Swift700000544
Justin Bieber680000665
Pink Floyd670000776
Red Hot Chili Peppers660000887
Ed Sheeran6500001098

Interpreting the results:

  • ROW_NUMBER(): Assigns sequential ranks to each artist based on their concert revenue. BTS gets rank 1, Beyonce rank 2, and so on.
  • RANK(): Assigns ranks, handling tied values with the same rank. Bruno Mars and Taylor Swift have the same revenue, so they both get rank 4, resulting in a +2 gap before Justin Bieber (rank 6).
  • DENSE_RANK(): Also handles ties with the same rank but does not skip ranks. After Bruno Mars and Taylor Swift, Justin Bieber follows at rank 5, maintaining a continuous ranking sequence.

Still curious about how the ranking functions work? Let's dive into another example.

Window Function Ranking with PARTITION BY and ORDER BY

In this example, we'll list the artists by country in the chronological order of their year of formation. To achieve this, we'll use the expression along with the clause.

In this query, each artist is ranked within their respective country based on the descending order of their year of formation.

The results look like this:

Justin BieberCanada680000111
The WeekndCanada620000222
BTSSouth Korea800000111
Super JuniorSouth Korea490000222
EXOSouth Korea480000333
Got7South Korea470000444
SHINeeSouth Korea470000544
Ed SheeranUnited Kingdom650000111
AdeleUnited Kingdom600000222
One DirectionUnited Kingdom580000333

The table is partitioned by country, meaning the ranking is reset for each country group. This is why the same rank may appear for different artists from different countries.

Let's explain how the functions differ in scenarios where there are differences in ranking:

  • Sia earned $510,000, and she is the only artist from Australia in the result set. As the sole artist, she receives the top rank #1 in all ranking functions.
  • Rihanna earned $550,000. Like Sia, she is the only artist from her country in the result set, so she receives the top rank #1 in all ranking functions.
  • Got7 and SHINEee have the same concert revenue. However, due to the different ranking functions used in the query, they receive different ranks:
    • ROW_NUMBER() assigns a unique number to each row, so Got7 gets rank 4, and SHINee gets rank 5.
    • RANK() assigns a unique rank to each distinct value and skips the next rank for ties. Both Got7 and SHINee share the rank 4, but the next rank is skipped, resulting in SHINee getting rank 4 as well.
    • DENSE_RANK() assigns a unique rank to each distinct value without skipping ranks for ties. Since Got7 and SHINee have the same revenue, they both receive rank #4.
  • Similarly, Taylor Swift and Bruno Mars have the same concert revenue but are assigned different rankings due to the way the ranking functions handle tied values.

Rank Window Function Interview Questions

Now, let's put these window function skills to the test, by solving multiple real SQL interview questions. Because of how often shows up in interviews, you've got a lot of work in front of you to do!

Spotify Rank Window Function SQL Interview Question

In this real Spotify Rank SQL Interview Question, you're asked to find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table.

Spotify Rank Window Function SQL Interview Question

If two or more artists have the same number of song appearances, they should be assigned the same ranking, and the rank numbers should be continuous (i.e. 1, 2, 2, 3, 4, 5). The expected rankings should look something like this:

Taylor Swift1
Bad Bunny2
Ed Sheeran3
Lady Gaga4

Walmart RANK SQL Interview Question

Assume you're given a table on Walmart user transactions. Based on their most recent transaction date, write a query that retrieve the users along with the number of products they bought.

Output the user's most recent transaction date, user ID, and the number of products, sorted in chronological order by the transaction date.

Google RANK SQL Interview Question

Here's a fun Google SQL interview question to try, about finding the sum of odd and even measurements. Don't forget the () SQL math function for the intermediate SQL tutorial – it'll come in handy!

What's Next: LEAD & LAG Window Functions

Next up, we'll look at two more popular window functions: LEAD and LAG

Next Lesson