NTILE, CUME_DIST & PERCENT_RANK

Sometimes you do not need "1st, 2nd, 3rd" but "top third vs bottom third" or "better than 80% of innings". NTILE cuts the sorted rows into buckets; CUME_DIST and PERCENT_RANK express a row's position as a fraction between 0 and 1. These examples use the 11 innings with a score (WHERE runs IS NOT NULL).

Definition

NTILE(n) divides the ordered rows of each partition into n buckets (groups) of as equal size as possible and returns the bucket number (1 to n) for each row. If the rows do not divide evenly, the earlier buckets get one extra row.

CUME_DIST() (cumulative distribution) returns the fraction of rows whose value is less than or equal to the current row's value: rows with value ≤ current ÷ total rows. Result is greater than 0 and up to 1.

PERCENT_RANK() returns the relative rank of a row: (RANK − 1) ÷ (total rows − 1). The first row is always 0 and the last is 1.

• Buckets are about row counts, not values — NTILE can put two tied scores into different buckets.
• CUME_DIST uses the position of the last tied row; PERCENT_RANK uses RANK, i.e. the first tied position. Ties always get the same value in both.

Syntax

NTILE(number_of_buckets) OVER ([PARTITION BY ...] ORDER BY ...)
CUME_DIST()              OVER ([PARTITION BY ...] ORDER BY ...)
PERCENT_RANK()           OVER ([PARTITION BY ...] ORDER BY ...)

ORDER BY is required; a frame is not allowed.

Example — NTILE(2), NTILE(3), NTILE(4)

SELECT id, player, runs,
    NTILE(2) OVER (ORDER BY runs DESC, id) AS b2,
    NTILE(3) OVER (ORDER BY runs DESC, id) AS b3,
    NTILE(4) OVER (ORDER BY runs DESC, id) AS b4
FROM innings
WHERE runs IS NOT NULL;

How the sizes are decided: 11 rows ÷ n buckets.

• NTILE(2): 11 ÷ 2 = 5 remainder 1 → sizes 6, 5 (high vs low scorers).
• NTILE(3): 11 ÷ 3 = 3 remainder 2 → sizes 4, 4, 3 (top / middle / bottom third).
• NTILE(4): 11 ÷ 4 = 2 remainder 3 → sizes 3, 3, 3, 2 (quartiles).

Notice the two 73s land in different buckets for NTILE(3) — NTILE cuts by row count and ignores ties. Use cases: customer segmentation (High / Medium / Low), quartiles, and splitting a big table into equal batches for processing.

Example — CUME_DIST and PERCENT_RANK

SELECT id, player, runs,
    ROUND(CUME_DIST()    OVER (ORDER BY runs), 3) AS cume_dist,
    ROUND(PERCENT_RANK() OVER (ORDER BY runs), 3) AS pct_rank
FROM innings
WHERE runs IS NOT NULL;

Working it out (11 rows, lowest first):

• CUME_DIST of 45: 4 rows have runs ≤ 45 (0, 26, 45, 45) → 4 ÷ 11 = 0.364. Both 45s get the value of the last tied position.
• PERCENT_RANK of 45: RANK of 45 is 3 → (3 − 1) ÷ (11 − 1) = 0.2.
• PERCENT_RANK of 59: RANK is 5 (both 45s are below it) → 4 ÷ 10 = 0.4.
• Lowest row: PERCENT_RANK = 0. Highest row: both = 1.

Reading it: Kohli's 82 has CUME_DIST 0.909 → "91% of innings scored 82 or less" — it is in the top 10%.

Note: if Dhoni's NULL is included, it counts as the lowest value in ASC order and shifts every number (NULL would get CUME_DIST 0.083 and the 0 would become 0.167). That is why these examples filter NULLs out first.

Common mistakes

• ❌ Expecting NTILE to keep ties together → ✅ it splits by row count; use DENSE_RANK or CASE ranges for value-based groups.
• ❌ Thinking NTILE(3) gives buckets of equal value range → ✅ equal row counts.
• ❌ Mixing up the two percent functions → ✅ CUME_DIST includes the current row's ties ("≤"), PERCENT_RANK starts at 0.
• ❌ Not handling NULL → it is ranked as the lowest value.

Interview tip

Q: How would you split customers into 4 equal groups by total spend? A: NTILE(4) OVER (ORDER BY total_spend DESC) — bucket 1 is the top 25% of customers. If the count does not divide evenly, the first buckets get one extra row.