Let us see how we can assign ranks using different rank functions.
Key Concept 1
If we have to assign ranks globally, we just need to specify ORDER BY.
If we have to assign ranks within a key then we need to specify PARTITION BY and then ORDER BY.
By default ORDER BY will sort the data in ascending order. We can change the order by passing DESC after ORDER BY.
We have 3 main functions to assign ranks - rank, dense_rank and row_number. We will see the differences between the 3 in a moment.
SELECT t.*, rank() OVER (PARTITION BY order_date ORDER BY revenue DESC) AS rnk
FROM daily_product_revenue t
ORDER BY order_date, revenue DESC
LIMIT 30
Key Concept 2
We will take a look at the difference between rank, dense_rank, and row_number functions.
We can use either of the functions to generate ranks when the rank field does not have duplicates. When the rank field has duplicates, then row_number should not be used as it generates a unique number for each record within the partition.
Rank will skip the ranks in between if multiple people get the same rank, while dense_rank continues with the next number.
SELECT
employee_id,
department_id,
salary,
rank() OVER (PARTITION BY department_id ORDER BY salary DESC) rnk,
dense_rank() OVER (PARTITION BY department_id ORDER BY salary DESC) drnk,
row_number() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) rn
FROM employees
ORDER BY department_id, salary DESC
LIMIT 50
Hands-On Tasks
- Execute the first SQL query provided using daily_product_revenue dataset.
- Run the second SQL query to understand the difference between rank, dense_rank, and row_number functions in assigning ranks.
Conclusion
In this article, we explored how to assign ranks using analytic functions such as rank, dense_rank, and row_number. We also saw the differences between these functions and when to use them. Practice assigning ranks with different scenarios to solidify your understanding.
Reference video for detailed explanation and practical demonstration: Analytic Functions - Ranking Tutorial