SQL Academy · Lesson

Bucketing with NTILE and Cume_Dist

Split rows into N equal-size buckets with NTILE, and compute cumulative distribution with CUME_DIST and PERCENT_RANK.

NTILE: Equal-Size Buckets

NTILE(n) splits ordered rows into n roughly-equal buckets:

SELECT id, total,
       NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;
-- 1 = bottom 25%, 2 = next, 3, 4 = top 25%

NTILE(10) for deciles, NTILE(100) for percentiles:

SELECT id, score,
       NTILE(100) OVER (ORDER BY score DESC) AS percentile
FROM students;
-- percentile 1 = top 1%