What’s the difference? — RANK() vs.DENSE_RANK() vs.ROW_NUMBER()

SQL 101 —How to Rank Rows in Byzer

Photo by Joshua Golde on Unsplash

What’s the difference between RANK() vs. DENSE_RANK() vs. ROW_NUMBER()? How are they different from each other?

Struggle to answer this question? Well, you have to read this blog!

Let’s go back to the real world and learn the difference from a common retail analytics use case. We’ll run some queries in Byzer Notebook so you can easily compare them to each other.

Retail Analytics: Finding Top X Best-Selling Products of This Season

Data is the backbone of the retail industry. What products should be sold in which store at what price is determined by data insights extracted from analyzing customer purchase history. One of the must-have metrics is Top X best-selling products within a timeframe.

In this blog, let’s run some SQL queries in Byzer Notebook to find the Top 3 Best-Selling Products of this season for a retailer.

We’ll use the sales table. It has the following columns:

• product - The name of the product.
• product_price - The price of the product.
• items_sold - The number of items sold.

Dummy Sales Data

Let’s create a dummy table in Byzer running the following code:

-- store dummy sales data as a JSON string
set sales='''
product,product_price,items_sold
a,      44.12,          6547
b,      100,            547
c,      12.47,          48
d,      12,             3254
e,      100,            547
f,      12,             3254
g,      12,             3254
h,      7.77,           147
''';
-- convert JSON string to a table
load csvStr.`sales` options header="true" and inferSchema="true"
as sales;

Pick Which One? RANK() or DENSE_RANK() or ROW_NUMBER()

To compute the most popular items, intuitively, we would sum the revenue up for each product, sort products in descending order of total revenue, and then pick the top 3 products. Alternatively, we can use the SQL window function — RANK() to compute the ranking and then filter data to show the top 3 items.

However, the results might surprise you as you run the query with RANK():

Where is the No.3 item?

Let’s run the following query that ranks the rows by revenue using the three ranking functions described above:

SQL — RANK(), DENSE_RANK(), ROW_NUMBER()

See the difference?

The ROW_NUMBER() function is self-explanatory, as you’ve already seen the data. It simply assigns a consecutive ranking to each row ordered by revenue. If two rows have the same value, they won’t have the same ranking.

SQL — ROW_NUMBER() function

The RANK() function creates a ranking of the rows based on the provided columns. It starts with assigning “1” to the first row in the order and then gives higher numbers to rows lower in the order. If rows have the same value, they’re ranked the same. However, the next spot is shifted accordingly. For example, if two rows are 2th (have the same rank), the next row will be 4th (i.e., 3rd doesn’t exist).

SQL — RANK() function

The DENSE_RANK() function is rather similar. The only difference is that it doesn’t leave gaps in the ranking values. Even though more than one row can have the same rank, the rank of the next row will be one plus the previous number. For example, if two rows are 2rd, the next row will be 3rd.

SQL — DENSE_RANK() function

So DENSE_RANK()is the right ranking function to show top results in this use case.

Here is the code:

SELECT
RANK() OVER(ORDER BY product_price * items_sold DESC) AS rank,
DENSE_RANK() OVER(ORDER BY product_price * items_sold DESC) AS dense_rank,
ROW_NUMBER() OVER(ORDER BY product_price * items_sold DESC) AS row_number,
product,
product_price * items_sold AS revenue
FROM sales
AS sales_rank;

What’s Byzer?

A simple analogy of Byzer

Go to this blog — Byzer 101, if you want to install Byzer on your laptop.

Want to see how Byzer how seamlessly Byzer Notebook integrates with SQL and Python? Go try this tutorial:

The EASIEST Way to Build and Visualise a Conversion Funnel

You can Do More with Less in Byzer !

Leaving feedback:

Please leave a comment here or join Slack to ask questions, get help, or discuss all things Byzer!

Last but not least, please share Byzer with data enthusiasts around you if you like this open-source project!

Thanks for reading!

Please share, subscribe to my email list, or follow me on Medium for upcoming blogs.