NTILE and Distribution

These three answer "where does this row sit in the distribution", rather than "what number is it".

NTILE

NTILE(N) divides the ordered window into N groups as evenly as possible and returns the current row's bucket number, from 1 to N. N must be a literal positive integer — or a ? parameter, user variable or stored routine variable — and cannot be NULL; NTILE(NULL) is not permitted. Buckets are assigned by position, so when the window has fewer rows than buckets the trailing buckets simply do not appear.

PERCENT_RANK and CUME_DIST

PERCENT_RANK() returns (rank - 1) / (rows - 1), where rank is the row's rank and rows is the number of rows in the partition — so the first row is always 0, while the last row reaches 1 only when the highest value is unique: peers share a rank, so a tie at the top leaves the final row below 1, and a single-row partition returns 0. CUME_DIST() returns the proportion of partition rows that precede or are peers of the current row, so the last row is 1 and no row is 0. Both take no arguments and both want an ORDER BY; without one every row is a peer of every other and the results degenerate.

Watch the peers

PERCENT_RANK and CUME_DIST respect peers — equal values get equal results. NTILE does not: it fills buckets by row position, so two identical values sitting on a bucket boundary land in different buckets.

-- Where each customer sits in the lifetime-spend distribution.
SELECT
  c.id,
  c.lifetime_cents,
  NTILE(4)       OVER w AS quartile,   -- 1 = lowest spend .. 4 = highest
  RANK()         OVER w AS rnk,
  PERCENT_RANK() OVER w AS pct_rank,   -- (rank - 1) / (rows - 1), first row 0
  CUME_DIST()    OVER w AS cume_dist   -- share of rows at or below this one
FROM customers AS c
WHERE c.lifetime_cents > 0
WINDOW w AS (ORDER BY c.lifetime_cents)
ORDER BY c.lifetime_cents;

-- The built-in and its definition, side by side. Identical output.
SELECT
  r.name,
  r.score,
  PERCENT_RANK() OVER (ORDER BY r.score)          AS builtin,
  (RANK() OVER (ORDER BY r.score) - 1)
    / (COUNT(*) OVER () - 1)                      AS by_hand
FROM exam_results AS r
ORDER BY r.score;

WITH bucketed AS (
  SELECT
    pr.region,
    pr.product_id,
    pr.revenue_cents,
    -- DESC ordering, so decile 1 is the best-performing tenth.
    NTILE(10) OVER (PARTITION BY pr.region
                    ORDER BY pr.revenue_cents DESC, pr.product_id) AS decile
  FROM product_revenue AS pr
)
SELECT region, product_id, revenue_cents
FROM bucketed
WHERE decile = 1               -- top 10% of products in each region
ORDER BY region, revenue_cents DESC;

-- MySQL 8.4 does not implement PERCENTILE_CONT or PERCENTILE_DISC.
-- CUME_DIST gives an inclusive percentile cut-off instead.
WITH d AS (
  SELECT response_ms,
         CUME_DIST() OVER (ORDER BY response_ms) AS cd
  FROM request_log
  WHERE logged_at >= NOW() - INTERVAL 1 DAY
)
SELECT MIN(response_ms) AS p95_ms   -- first value at or above the 95th percentile
FROM d
WHERE cd >= 0.95;