SQL PERCENT_RANK

What is the SQL PERCENT_RANK function?

PERCENT_RANK() returns the relative rank of a row within its partition as a decimal between 0 and 1 inclusive.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PERCENT_RANK: PostgreSQL, MySQL 8.0+, SQL Server 2012+, Oracle 10g+, Snowflake, Redshift, BigQuery, SQLite 3.25+ (with window support)

SQL PERCENT_RANK Full Explanation

PERCENT_RANK is a window (analytic) function specified in the SELECT list or ORDER BY clause. It calculates the percentage rank of the current row compared to other rows in the same partition, using the formula (rank - 1) / (total_rows_in_partition - 1). The first row in each partition always returns 0, and the last row returns 1 when at least two rows exist. If the partition contains only one row, PERCENT_RANK returns 0 to avoid division by zero. Ties receive the same rank, so rows with identical sort keys share the same PERCENT_RANK value. The function operates after the WHERE, GROUP BY, and HAVING phases and before the final ORDER BY, making it ideal for analytic reporting without altering result cardinality.

SQL PERCENT_RANK Syntax

SELECT
  column_list,
  PERCENT_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
  ) AS percent_rank
FROM table_name;

SQL PERCENT_RANK Parameters

Example Queries Using SQL PERCENT_RANK

-- Example 1: Percent rank of employees' salaries within each department
SELECT
  department_id,
  employee_id,
  salary,
  PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pct_rank
FROM employees;

-- Example 2: Identify top 10 percent highest revenue orders company-wide
WITH ranked AS (
  SELECT
    order_id,
    revenue,
    PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pct_rank
  FROM orders
)
SELECT *
FROM ranked
WHERE pct_rank <= 0.10;

Expected Output Using SQL PERCENT_RANK

Each row gains an additional column (pct_rank) showing a decimal between 0 and 1 that indicates its position relative to other rows in the defined window
In Example 1, the lowest salary per department shows 0 and the highest shows 1 (if multiple rows exist)
Example 2 filters rows whose pct_rank is 0
10 or lower, returning the top 10 percent of orders by revenue

Use Cases with SQL PERCENT_RANK

Produce percentile-style rankings without complex percentile functions
Highlight top or bottom x percent performers in reports
Compare relative position of values across groups (e.g., salary within department)
Build heat-maps or conditional formatting in BI tools based on relative rank
Feed downstream queries that need a proportionate rank rather than absolute ranks

Common Mistakes with SQL PERCENT_RANK

Forgetting to include ORDER BY inside the OVER clause, which causes an error
Expecting continuous values when ties exist; identical sort keys share the same percentile
Assuming the last row is always 1; it is 1 only when the partition has at least two rows
Confusing PERCENT_RANK with CUME_DIST; PERCENT_RANK excludes the current row in the numerator formula, leading to slightly lower values

First Introduced In

SQL:2003 analytic extensions

Frequently Asked Questions

What does PERCENT_RANK return?

It returns a decimal between 0 and 1 indicating the row's relative position within its partition.

What is the formula behind PERCENT_RANK?

The function uses (rank - 1) divided by (total_rows_in_partition - 1).

When should I use PERCENT_RANK over RANK?

Use PERCENT_RANK when you need a normalized value that is independent of partition size, enabling easy comparisons across groups.

Can I use PERCENT_RANK in a WHERE clause?

Yes, wrap it in a subquery or CTE first, then filter on the computed column.