SQL — RANK Function

What is a RANK function? While practicing technical interview questions from Data Interview Q’s, I came upon SQL questions involving rank functions. Below is an example of a technical question that utilize rank function.

Data Interview Q’s Question

You might ask, how do you use a RANK() function? Here we go in detail on what we use it for and how to use it. The purpose of a RANK() function is to assign a number order to each row within a partition of a result set. Displayed below is the syntax for the function RANK().

RANK() OVER (     
    [PARTITION BY partition_expression, ... ]     
    ORDER BY sort_expression [ASC | DESC], ... 
)

• PARTITION BY — Breaks the rows of the result set into partitions.
• ORDER BY — Order the rows in each partition.

Based off of the practice technical question shown above, we can now discuss about how to use the RANK() function. Below is an example of how I solved the problem above using RANK() function.

SELECT AP.distributor_id, 
       O.product_id,
       O.no_units 
       total_cost, 
       total_revenue, 
       total_margins, 
       RANK() OVER (
                    ORDER BY total_revenue DESC) AS rank_revenue,
       RANK() OVER (
                    ORDER BY total_margins DESC) AS rank_margins,
       RANK() OVER (
                    ORDER BY total_cost DESC) AS rank_cost
FROM(SELECT AP.distributor_id,
            O.product_id, 
            O.no_units
            no_units * sell_price AS total_revenue, 
            no_units * buy_price AS total_cost, 
            (sell_price-buy_price) * (no_units/sell_price) AS
            total_margins 
            FROM all_products AS AP
            JOIN orders AS O  
            ON AP.product_id = O.product_id         
            )

As requested above we want to display the distributor id, product id, total cost, total revenue, total margin, ranking of total cost, ranking of total revenue and ranking of total margin. Before we can do the ranking of total cost, total revenue and total margin, we need to first calculate them. Therefore in our subquery we joined the two tables together to calculate total revenue, total cost and total margin.

Now that we have total revenue, total cost and total margin, we can then apply the RANK() function. In this particular example, we won’t be partitioning or grouping any data together because we want to identify the top selling items that have the highest margins. As you can see above we didn’t use the partition syntax above and only the ORDER BY syntax. Although PARTITION wasn’t used in the example above, here’s another example of how PARTITION would look like if we use the same problem above.

RANK() OVER (PARTITION BY product_id
             ORDER BY total_revenue DESC) AS rank_revenue

This set of code will order the total revenue for each set of product ID. As you can see in the table below, the RANK() function above partitions the product ID into 123, 234, 345, and 456. Within each partition we would order the total revenue and rank them in order in our new column called rank_revenue.

╔════════════╦══════════════╦══════════════╗
║ product_iD ║ total_revenue║ rank_revenue ║
╠════════════╬══════════════╬══════════════╣
║ 123        ║ 900          ║ 1            ║
║ 123        ║ 800          ║ 2            ║
║ 123        ║ 700          ║ 3            ║
║ 234        ║ 950          ║ 1            ║
║ 234        ║ 850          ║ 2            ║
║ 234        ║ 750          ║ 3            ║
║ 234        ║ 650          ║ 4            ║
║ 345        ║ 1000         ║ 1            ║
║ 456        ║ 900          ║ 1            ║
║ 456        ║ 800          ║ 2            ║
╚════════════╩══════════════╩══════════════╝

Hopefully this clears up any questions you may have about how to use the RANK() function in your future SQL query. If you have any questions feel free to email me or leave a comment below.