ROW_NUMBER() vs RANK(): Understanding the Differences

With Google SQL Interview Example

Photo by Rubaitul Azad on Unsplash

SQL, a powerful language for managing databases, offers a variety of functions to help you retrieve and analyze data. Among these functions, row_number() and rank() are two that often leave me scratching my head.

The confusion stems from their similar-sounding names and somewhat overlapping functionalities. In this blog post, I’ll dissect these functions and shed light on their key differences.

To make things even more intriguing, I’ll be diving into a real-world example inspired by the Google’s coding interview question. So, grab your favorite SQL editor, as I journey through the world of row_number() and rank(), untangling the web of confusion along the way.

Example

Please read this question and write your own query if you want.

Q. Find the email activity rank for each user.

Email activity rank is defined by the total number of emails sent. The user with the highest number of emails sent will have a rank of 1, and so on. Output the user, total emails, and their activity rank. Order records by the total emails in descending order. Sort users with the same number of emails in alphabetical order. In your rankings, return a unique value (i.e., a unique rank) even if…