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:

ROW_NUMBER vs. RANK vs. DENSE_RANK

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.

Schema:

Table:

Window Function Ranking with ORDER BY

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

Results:

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:

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.

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:

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