[SQL Interview] Differences Between RANK() and DENSE_RANK()

In a data analyst interview, various questions can be asked to evaluate your data analysis and SQL query skills. This time, let’s look at a question about the differences between RANK() and DENSE_RANK() functions.

Question: Explain the difference between RANK() and DENSE_RANK() functions and illustrate how these two functions can be used in a real business scenario.

RANK() Function

The RANK() function returns the rank of each row within the partition of a result set. Rows with equal values receive the same rank, but the next rank is skipped.

RANK() OVER (PARTITION BY partition_column ORDER BY order_column)

PARTITION BY partition_column: The column to partition the data by (optional)
ORDER BY order_column: The column to order the data by (required)

DENSE_RANK() Function

The DENSE_RANK() function also returns the rank of each row within the partition of a result set. Rows with equal values receive the same rank, but the next rank is not skipped; it is incremented by one.

DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column)

PARTITION BY partition_column: The column to partition the data by (optional)
ORDER BY order_column: The column to order the data by (required)

These two functions are often used for ranking calculations and are very useful in data analysis.

Example: Product Sales Ranking Analysis

A company wants to analyze the monthly sales data of products. They need to rank the products based on their sales and understand the differences when products have the same sales using RANK() and DENSE_RANK() functions.

Example Using RANK() Function:

SELECT product_name, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM dataset.sales;

Example Using DENSE_RANK() Function:

SELECT product_name, sales, DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank FROM dataset.sales;

Example Results:

product_name	sales	rank	dense_rank
Product A	100	1	1
Product B	100	1	1
Product C	90	3	2
Product D	80	4	3

In this example:

The RANK() function assigns the same rank to products with equal sales, but skips the next rank.
The DENSE_RANK() function assigns the same rank to products with equal sales, but the next rank is incremented by one.

Business Use Cases

Using RANK() Function:

Case: Determining Salesperson Incentives

A company wants to evaluate the monthly sales performance of salespeople to distribute incentives. When sales figures are the same, the same rank is given, but the next rank is skipped to ensure that the top performers receive higher incentives.

SELECT sales_person, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM sales_data;

Results Example:

sales_person	total_sales	sales_rank
Alice	1000	1
Bob	1000	1
Carol	900	3
Dave	850	4

Using DENSE_RANK() Function:

Case: Measuring Product Popularity

An e-commerce company wants to measure product popularity. They need to assign ranks to products based on their popularity scores and ensure that the ranks are sequential without gaps.

SELECT product_name, category, popularity_score, DENSE_RANK() OVER (PARTITION BY category ORDER BY popularity_score DESC) AS popularity_rank FROM product_data;