NTILE, PERCENT_RANK & CUME_DIST

Similar names, different questions

WITH scores(id, score) AS (
    VALUES (1, 10), (2, 20), (3, 20), (4, 40)
)
SELECT id, score,
       NTILE(2) OVER (ORDER BY score, id) AS half,
       PERCENT_RANK() OVER (ORDER BY score) AS relative_rank,
       CUME_DIST() OVER (ORDER BY score) AS cumulative_fraction
FROM scores
ORDER BY score, id;

NTILE divides rows as evenly as possible. It can split tied scores between buckets. PERCENT_RANK uses the starting rank of a peer group; CUME_DIST includes the whole peer group in the cumulative fraction.

Ordering changes the interpretation

With ascending scores, a high cumulative fraction corresponds to a high score. Descending order reverses the perspective. Exclude null scores or specify their placement according to the intended population.

Do not add a unique ID to percent-rank ordering if equal scores should be peers. The ID is appropriate for deterministic NTILE assignment when ties must be broken.

Exercise

If every score is identical, what happens?