PERCENT_RANK and CUME_DIST: Relative Ranking Instead of Fixed Positions

1. Why absolute rank is not always the right answer

RANK and DENSE_RANK return an absolute position within a sorted set, telling you a value is the third highest or the seventh within its partition. That information is valuable as long as the size of the overall set is known and stable, for example a fixed length top ten list. But once the row count changes from query to query, because a time range, a filter, or a category varies, an absolute position quickly loses meaning, since rank twelve out of a hundred rows means something completely different from rank twelve out of twelve thousand rows.

This is exactly where PERCENT_RANK and CUME_DIST come in. Both return a value between zero and one instead of an integer, expressing position relative to the total size of the partition. A result of 0.95 always means the same thing regardless of the absolute row count, namely that the value sits near the top of the distribution. This normalization makes both functions particularly useful for comparisons across groups of very different sizes, for example comparing sales figures between stores with widely differing customer counts.

2. The PERCENT_RANK formula in detail

PERCENT_RANK is calculated as (rank minus one) divided by (total number of rows in the partition minus one), where rank is the value RANK would return for the same row. The first row in a partition always receives the value zero, regardless of the total row count, because rank for the first row is always one, and one minus one divided by anything is always zero. The last row correspondingly always receives the value one, unless ties push that position elsewhere.

Important to note, PERCENT_RANK handles ties the same way RANK does, meaning equal values receive the same rank, and subsequent ranks are skipped accordingly. That means when many identical values sit at the top of the distribution, several rows receive the same PERCENT_RANK value, while the next distinct group shows a visible jump in the result. For a partition with only a single row, the denominator is zero, and in that case the SQL standard defines the return value as zero percent, that is 0.0.

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) AS salary_rank,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS salary_percent_rank
FROM employees
ORDER BY department, salary;

3. CUME_DIST: cumulative distribution instead of a rank base

CUME_DIST differs from PERCENT_RANK in one decisive detail: instead of building on rank, CUME_DIST directly counts how many rows have a value less than or equal to the current value, and divides that count by the total number of rows in the partition. The formula is therefore the number of rows with a sort value less than or equal to the current value, divided by the total row count. That means the result range is always strictly greater than zero up to exactly one, never zero.

This difference has a noticeable effect on ties. While with PERCENT_RANK all rows sharing the same value share the same, potentially very low rank, with CUME_DIST all rows sharing the same value automatically receive the highest shared proportion value of that group, because the count includes every row with an equal or smaller value. In practice this means CUME_DIST answers what share of the total set is at most as large as the current value, while PERCENT_RANK expresses relative position within the sort order instead.

SELECT
    employee_id,
    department,
    salary,
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary) AS salary_cume_dist
FROM employees
ORDER BY department, salary;

4. PERCENT_RANK and CUME_DIST compared directly with ties

The practical difference between the two functions only becomes fully visible with duplicate values. Assume a partition of ten rows where three rows share the same, lowest value. With PERCENT_RANK all three rows receive the value zero, because their shared rank is one and the formula computes (one minus one) divided by nine. With CUME_DIST the same three rows instead receive the value 0.3, because three out of ten rows have a value less than or equal to the current value.

This divergence shows that the two functions answer different questions, even though they look similar on the surface. PERCENT_RANK answers how far a row sits from the first and last position in the sort order, while CUME_DIST answers what share of the total set reaches at most this value. For percentile reports where users typically ask what percentage range a value falls into, CUME_DIST is usually the more intuitive choice, since it reads directly as a proportion.

5. How this differs from NTILE: continuous instead of fixed buckets

NTILE divides a partition into a fixed number of equally sized groups, for example four quartiles or ten deciles, and each row receives an integer indicating group membership. That is useful when a fixed number of categories is genuinely needed, for example segmenting customers into four equally sized groups. The downside is that NTILE provides no further differentiation within a group, two values in the same group can sit very differently far from the group boundary without that showing up in the result.

PERCENT_RANK and CUME_DIST instead deliver a continuous, stepless ranking with no predefined bucket count. Instead of saying a customer belongs to the top quartile, CUME_DIST directly states that a customer sits in the top 1.5 percent of the distribution, which is often more informative for fine grained analyses such as bonus calculations or individual percentile figures in a dashboard than a coarse bucket assignment. In practice the two approaches often complement each other: NTILE for coarse categorization, PERCENT_RANK or CUME_DIST for the exact percentage within a report.

6. Practical example: salary benchmarking without a fixed bucket count

A realistic use case is an internal salary benchmark where HR wants to know what percentile an individual salary falls into within its job family, without predefining a fixed number of salary bands. A question like does this salary sit in the top ten percent or closer to the middle can be answered directly with CUME_DIST, without any additional bucket logic.

The following query computes both the relative rank and the cumulative distribution for every position within a job family, so both perspectives are available in the same result. Rounding to two decimal places makes the result readable for a presentation without changing the underlying precision of the calculation.

SELECT
    employee_id,
    job_family,
    salary,
    ROUND(CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary)::numeric, 2)
        AS salary_percentile,
    CASE
        WHEN CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary) >= 0.9
            THEN 'Top 10 percent'
        WHEN CUME_DIST() OVER (PARTITION BY job_family ORDER BY salary) <= 0.1
            THEN 'Bottom decile'
        ELSE 'Mid range'
    END AS bucket_label
FROM employees
ORDER BY job_family, salary;

7. Building per group percentiles correctly with PARTITION BY

Both PERCENT_RANK and CUME_DIST accept a PARTITION BY clause like any other window function, restricting the calculation to individual groups. Without PARTITION BY, the calculation applies to the entire result set, which produces misleading results for heterogeneous groups, for example different departments with widely varying salary levels, since a high salary within a lower paid department can falsely appear average once mixed with a higher paid department.

A common mistake in practice is forgetting PARTITION BY or choosing it incorrectly when several dimensions matter at once, for example department and location. In such cases the PARTITION BY clause should include every dimension within which a meaningful comparison should take place, while cross partition comparisons across the whole set require a separate query without partitioning, or with a deliberately coarser partitioning scheme.

8. Edge cases with small partitions and null values

With very small partitions, both functions return mathematically correct but often practically uninformative results. A partition with exactly two rows returns either zero or one for PERCENT_RANK, there are no intermediate values, because the denominator in the formula can only take the value one. For a partition with only a single row, PERCENT_RANK returns zero by definition, while CUME_DIST for that same row always returns one, since one hundred percent of the rows have a value less than or equal to the only value present.

Null values in the sort column also deserve special attention. Most databases sort null values either at the start or the end of the ordering depending on configuration, which directly affects the computed PERCENT_RANK or CUME_DIST value. If this behavior matters for the business logic, an explicit NULLS FIRST or NULLS LAST clause in the window function's ORDER BY is recommended, rather than relying on the database's default behavior, which genuinely differs between systems.

SELECT
    employee_id,
    bonus_eligible,
    PERCENT_RANK() OVER (
        ORDER BY bonus_eligible NULLS LAST
    ) AS bonus_percent_rank
FROM employees;

9. Availability across different database systems

PERCENT_RANK and CUME_DIST are part of the SQL standard and have long been fully implemented in PostgreSQL, Oracle, and SQL Server, each with identical syntax and identical tie handling. MySQL only supports both functions since version 8.0 as part of the general introduction of window functions, in older MySQL versions they have to be manually recreated using subqueries and COUNT aggregations, which means noticeably more code and worse readability.

SQLite has also supported both functions since a comparatively early version as part of its window function extension, making them usable for embedded applications and local analytics as well. For portable code intended to run across multiple database systems, it is still worth checking the concrete minimum version, especially when older MySQL installations are within the target range, since a functional workaround using windowed aggregation with COUNT and suitable ORDER BY logic becomes necessary there.

10. Summary

11. FAQ: PERCENT_RANK and CUME_DIST: Key Takeaways