SQL PERCENT_RANK Tutorial 🎯

Welcome to the SQL PERCENT_RANK tutorial! In this lesson, we'll explore the PERCENT_RANK function, a powerful tool for ranking records in SQL. Let's dive in! 🐳

What is PERCENT_RANK? 💡

The PERCENT_RANK function in SQL calculates the rank of a row in a result set, with each rank being the proportion of rows that come before it. This is different from the traditional RANK function, which simply assigns a unique number to each row without considering the order of other rows.

Why use PERCENT_RANK? 📝

PERCENT_RANK is useful when you want to compare the relative performance or position of a row to the entire dataset. For instance, if you have a list of students' test scores and want to find the percentage of students who scored better or worse than a particular student, you would use PERCENT_RANK.

How to use PERCENT_RANK? 💻

Syntax

The syntax for the PERCENT_RANK function is as follows:

sql

PERCENT_RANK() OVER ([ORDER BY expression] | [PARTITION BY expression] [ORDER BY expression])

expression is any valid SQL expression that determines the order of rows.
PARTITION BY expression divides the result set into groups based on the specified expression.

Example

Let's consider a simple table of employees and their sales:

sql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    sales FLOAT
);

INSERT INTO employees (id, name, sales)
VALUES (1, 'Alice', 10000),
       (2, 'Bob', 8000),
       (3, 'Charlie', 12000),
       (4, 'Dave', 6000);

To find the PERCENT_RANK of each employee's sales, we can use the following SQL query:

sql

SELECT id, name, sales, PERCENT_RANK() OVER (ORDER BY sales DESC) AS percent_rank
FROM employees;

This will output:

id | name | sales | percent_rank
---|------|-------|-------------
3  | Charlie | 12000 | 1.0000000000
1  | Alice  | 10000 | 0.6666666667
2  | Bob    | 8000  | 0.3333333333
4  | Dave   | 6000  | 0.0000000000

As you can see, each employee's sales rank is their proportion in the list of sales. Charlie, with the highest sales, has a PERCENT_RANK of 1, while Dave, with the lowest sales, has a PERCENT_RANK of 0.

Quick Quiz

Question 1 of 1

Which employee has the highest PERCENT_RANK in the above example?

Partitioning with PERCENT_RANK 📝

You can also use the PARTITION BY clause to divide the result set into groups and calculate the PERCENT_RANK within each group.

For example, let's say we want to find the PERCENT_RANK of each employee's sales within their respective departments:

sql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    sales FLOAT,
    department VARCHAR(50)
);

INSERT INTO employees (id, name, sales, department)
VALUES (1, 'Alice', 10000, 'Sales'),
       (2, 'Bob', 8000, 'Sales'),
       (3, 'Charlie', 12000, 'Sales'),
       (4, 'Dave', 6000, 'Marketing'),
       (5, 'Eve', 9000, 'Marketing');

To calculate the PERCENT_RANK of each employee's sales within their department, we can use the following SQL query:

sql

SELECT id, name, sales, department, PERCENT_RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS percent_rank
FROM employees;

This will output:

id | name | sales | department | percent_rank
---|------|-------|------------|--------------
1  | Alice | 10000 | Sales      | 1.0000000000
2  | Bob   | 8000  | Sales      | 0.3333333333
3  | Charlie | 12000 | Sales      | 0.6666666667
4  | Dave   | 6000  | Marketing  | 0.0000000000
5  | Eve    | 9000  | Marketing  | 1.0000000000

Now, each employee's sales rank is their proportion within their department. Alice and Eve have a PERCENT_RANK of 1 in their respective departments, while Dave and Bob have a PERCENT_RANK of 0.

Quick Quiz

Question 1 of 1

What is the PERCENT_RANK of Bob within the 'Sales' department?

Conclusion 📝

In this tutorial, you learned about the SQL PERCENT_RANK function and how it can be used to find the rank of a row as a proportion of the entire result set or within a specific group. Understanding PERCENT_RANK will help you make more insightful comparisons and analyses in your SQL queries. Happy coding! 🤖

Remember to practice using PERCENT_RANK in your own projects to solidify your understanding. As always, if you have any questions, feel free to reach out to our community! 🎉

💡 Pro Tip: Don't forget to optimize your queries for performance by properly indexing your tables and carefully crafting your WHERE clauses.