NTILE, PERCENT_RANK and CUME_DIST: where do you stand?

Ranks say "third". These say "top quarter" or "better than 89% of the field":

The test case

2025 batters who faced at least 80 balls: their runs, balls, strike rate (one decimal), which quarter of the strike-rate table they're in (1 = fastest), and their PERCENT_RANK and CUME_DIST (two decimals, higher = faster).

Expected result

19 batters don't split evenly into 4: NTILE makes the first buckets one bigger, so it's 5, 5, 5 and 4. Tyler Vance at number 5 for Tampa tops the strike rates, just ahead of Karthik Raman: proof that you don't need many balls to be a nuisance.

Way 1: the three functions

with faced as (
  select batter_id, count(*) as balls, sum(runs_batter) as runs
  from   cr_balls_v
  where  season = 2025 and (extra_type is null or extra_type <> 'WD')
  group  by batter_id
  having count(*) >= 80
)
select p.player_name, f.runs, f.balls,
       round(100 * f.runs / f.balls, 1)                                         as sr,
       ntile(4)       over (order by f.runs / f.balls desc, p.player_name)      as quartile,
       round(percent_rank() over (order by f.runs / f.balls), 2)                as pr,
       round(cume_dist()    over (order by f.runs / f.balls), 2)                as cd
from   faced f
join   cr_players p on p.player_id = f.batter_id
order  by sr desc, p.player_name;

NTILE sorts fastest first (DESC) so bucket 1 is the fastest; the other two sort ascending so that higher means faster. Each window can have its own order.

Way 2: the arithmetic behind them

with faced as (
  select batter_id, count(*) as balls, sum(runs_batter) as runs
  from   cr_balls_v
  where  season = 2025 and (extra_type is null or extra_type <> 'WD')
  group  by batter_id
  having count(*) >= 80
),
ranked as (
  select p.player_name, f.runs, f.balls,
         row_number() over (order by f.runs / f.balls desc, p.player_name) as rn,
         rank()       over (order by f.runs / f.balls)                     as rnk_up,
         count(*)     over (order by f.runs / f.balls)                     as at_or_below,
         count(*)     over ()                                              as n
  from   faced f
  join   cr_players p on p.player_id = f.batter_id
),
sized as (
  select r.*, trunc(n / 4) as q, mod(n, 4) as extra from ranked r     -- 19 rows: q = 4, extra = 3
)
select player_name, runs, balls,
       round(100 * runs / balls, 1)                                        as sr,
       case when rn <= extra * (q + 1) then ceil(rn / (q + 1))             -- the first buckets get one more row
            else extra + ceil((rn - extra * (q + 1)) / q)
       end                                                                 as quartile,
       round((rnk_up - 1) / (n - 1), 2)                                    as pr,
       round(at_or_below / n, 2)                                           as cd
from   sized
order  by sr desc, player_name;

What the functions mean, written out:

• NTILE(4): with 19 rows, 4 buckets of 4 and 3 left over. The first 3 buckets each get one of the leftovers (5 rows), the last gets 4.
• PERCENT_RANK: (rank − 1) / (n − 1).
• CUME_DIST: rows at or below this one, divided by n. count(*) over (order by ...) is a running count with the default RANGE frame, so tied rows are counted together: exactly "at or below".

Worth understanding, not worth maintaining. Use Way 1.

19c and 26ai

The same on both.

Gotchas

• NTILE splits rows, not values. Two batters with the same strike rate can land in different quartiles. Add a tie-breaker so it's at least repeatable.
• PERCENT_RANK of the first row is 0, never "1st percentile". CUME_DIST never reaches 0.
• Small groups make coarse percentiles. With 19 batters, each step is about 5%.