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.
Bucketing with NTILE and Cume_Dist is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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%Percentile Buckets
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%PERCENT_RANK
The relative rank as a float in [0, 1]:
SELECT id, score,
PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM students;
-- 0 for the lowest, 1 for the highest.CUME_DIST
The fraction of rows ≤ the current row's value:
SELECT id, score,
CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM students;
-- "What share of students scored ≤ this one?"Percentile Statistics with PERCENTILE_CONT
Compute the value at a percentile (continuous interpolation):
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total) AS p99
FROM orders;PERCENTILE_DISC
Like PERCENTILE_CONT but returns an actual existing value rather than interpolated:
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total)NTILE on Partitions
Bucket per user, per category, etc.:
SELECT user_id, total,
NTILE(4) OVER (PARTITION BY user_id ORDER BY total) AS user_quartile
FROM orders;Histogram via NTILE
Build histogram bins:
SELECT bucket, MIN(total) AS bucket_min, MAX(total) AS bucket_max, COUNT(*)
FROM (
SELECT NTILE(10) OVER (ORDER BY total) AS bucket, total
FROM orders
) sub
GROUP BY bucket
ORDER BY bucket;Aggregating Per Bucket
After bucketing, compute stats per bucket:
SELECT user_quartile, COUNT(*) AS users, AVG(total) AS avg_total
FROM (
SELECT user_id, NTILE(4) OVER (ORDER BY total) AS user_quartile, total
FROM orders
) sub
GROUP BY user_quartile;Combining Window Functions
Mix in one query for a rich report:
SELECT id, total,
NTILE(4) OVER w AS quartile,
PERCENT_RANK() OVER w AS pct_rank,
AVG(total) OVER w AS overall_avg
FROM orders
WINDOW w AS (ORDER BY total);When NTILE Misleads
Ties on the ORDER BY can produce uneven bucket sizes (NTILE doesn't respect ties). Use a tiebreaker column.
Recap
NTILE + PERCENT_RANK + CUME_DIST cover distribution analysis.
- NTILE for quantile buckets
- PERCENTILE_CONT/DISC for p50/p95/p99 stats
- Combine with PARTITION BY for per-group analysis
Quick Check
You need p95 latency from a column of response times. Which function?
Frequently asked questions
Is the “Bucketing with NTILE and Cume_Dist” lesson free?
Yes — the full text of “Bucketing with NTILE and Cume_Dist” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “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. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Bucketing with NTILE and Cume_Dist” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Frame Clauses: ROWS vs RANGE
- Lag/Lead with Frame Windows
- Bucketing with NTILE and Cume_Dist
- Real-World Reporting Patterns