SQL Series #3: ROW_NUMBER() Window Function

Problem Statement: Imagine you work for a retail company, and you want to analyze the performance of your salespeople. You have a table sales that records each sale made by your sales team, including the salesperson's ID, the sale date, and the sale amount.

You need to generate a report that ranks each sale for every salesperson based on the sale amount. This can help you identify top-performing salespeople for each period or determine who made the most significant sales in a given timeframe.

Input

Solution

ROW_NUMBER() OVER (...):

• ROW_NUMBER(): This function generates a unique number for each row within a specified partition. The numbering starts at 1 for the first row in each partition and increments sequentially.

PARTITION BY salesperson_id:

• This clause divides the result set into partitions based on the salesperson_id. Each partition represents a distinct salesperson. The ROW_NUMBER() function will reset its numbering for each partition.
• For example, all sales made by salesperson_id = 1 are grouped together, and the ROW_NUMBER() function will start from 1 for this group, and similarly for other salespersons.

ORDER BY amount DESC:

• Within each partition (i.e., for each salesperson), the rows are ordered by the amount column in descending order.
• This means that the sale with the highest amount will receive the rank of 1, the next highest will receive 2, and so on. The DESC keyword specifies descending order.

Output