What is the difference between PERCENT_RANK() and CUME_DIST() for tied rows?

## Two ways to say "where in the distribution am I?" `PERCENT_RANK()` and `CUME_DIST()` both express a row's position in its window partition as a fraction rather than an ordinal. Both take no arguments, both need a window `ORDER BY` to mean anything, and both are peer-aware: rows whose ordering values compare equal always receive the same result. That last property is what separates them from `ROW_NUMBER()`, which would give near-identical rows visibly different scores. ## PERCENT_RANK() The definition is arithmetic on `RANK()`: PERCENT_RANK() = (RANK() - 1) / (number of rows in the partition - 1) Because `RANK()` is 1 for the first peer group, `PERCENT_RANK()` is 0 for every row in it — always, no matter how many rows tie there. The maximum is 1, reached by the last peer group. The measure therefore answers "what fraction of the other rows am I strictly ahead of?" and its range is 0 to 1 inclusive. A single-row partition would divide by zero, so the standard defines the result as 0 in that case. ## CUME_DIST() The definition counts rows instead: CUME_DIST() = (rows preceding or peer with the current row) / (number of rows in the partition) The numerator runs through the **end** of the peer group, so `CUME_DIST()` answers "what fraction of rows are at or below me?" — the cumulative distribution in the statistical sense. It is never 0, because the current row always counts itself, and it is exactly 1 for the final peer group. ## Worked comparison Take four rows with values 10, 20, 20, 40 ordered ascending. `RANK()` gives 1, 2, 2, 4. - `PERCENT_RANK()`: (1−1)/3 = 0, (2−1)/3 ≈ 0.333, ≈ 0.333, (4−1)/3 = 1. - `CUME_DIST()`: 1/4 = 0.25, 3/4 = 0.75, 0.75, 4/4 = 1. Notice both give the tied 20s one shared value, but they disagree about what that value is: `PERCENT_RANK()` reports the tie group's starting position, `CUME_DIST()` its ending position. The gap between them is exactly the width of the tie. ```sql SELECT student, score, PERCENT_RANK() OVER (ORDER BY score) AS pr, CUME_DIST() OVER (ORDER BY score) AS cd FROM exam_results; ``` ## Choosing between them Use `CUME_DIST()` when the sentence you want to write is "this row is in the top 10 percent" or "90 percent of the population scores at or below this" — it is the cumulative-distribution reading people expect from a percentile. Use `PERCENT_RANK()` when you want a normalised rank that starts at 0 for the best (or worst, depending on sort direction) row, for example to rescale positions onto a 0-to-1 axis for comparison across partitions of very different sizes. Both are relative to the partition, so with `PARTITION BY` each group is scored against itself. That makes them a natural way to compare a row against its own cohort rather than the whole table. ## Relationship to the other ranking functions All of these are computed over the whole ordered partition — `PARTITION BY` and the window `ORDER BY` fully determine the answer, and there is no frame to specify. `PERCENT_RANK()` is literally a rescaled `RANK()`, which is why it inherits `RANK()`'s gap behaviour: a wide tie near the bottom pushes the next distinct value's fraction up sharply. `CUME_DIST()` has no gap behaviour of its own; it just counts rows. A common confusion is with `NTILE(n)`, which returns a bucket number rather than a fraction and guarantees near-equal bucket sizes rather than a faithful position. If you need a continuous percentile score, these two functions are the right tools; if you need cohorts of equal size, `NTILE` is.