NTILE for Bucketing
Splitting rows into quartiles and percentile bands.
NTILE for Bucketing is a free SQL Interview Prep 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
When You Need Equal-Size Buckets
Interviewers ask: "Split customers into four equal spending groups," or "Which decile is each row in?" The tool is NTILE.
NTILE(n) distributes ordered rows into n buckets as evenly as possible and labels each row with its bucket number, 1 through n. This lesson covers how it splits, how it handles uneven counts, and where it differs from ranking.
Basic NTILE Syntax
NTILE(4) over rows ordered by a value produces quartiles. Like all window functions it needs an OVER clause; the ORDER BY inside decides which rows land in the low buckets versus the high ones.
Ordering ascending puts the smallest values in bucket 1; ordering descending flips it.
SELECT
customer_id,
total_spend,
NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customers;How NTILE Distributes Rows
With 12 rows and NTILE(4), each bucket gets exactly 12 / 4 = 3 rows. Bucket 1 = the lowest 3 values, bucket 4 = the highest 3.
The key idea: NTILE splits by count of rows, not by value ranges. Two buckets can cover very different value spans as long as they hold the same number of rows.
Uneven Division
What if rows do not divide evenly? With 10 rows and NTILE(4), 10 / 4 = 2 remainder 2. NTILE gives the earlier buckets the extra rows.
- Bucket 1: 3 rows
- Bucket 2: 3 rows
- Bucket 3: 2 rows
- Bucket 4: 2 rows
So bucket sizes differ by at most one, and the larger buckets come first. This exact rule is a favorite interview detail.
NTILE Ignores Ties in Values
Critical gotcha: NTILE does not keep equal values in the same bucket. It fills by position, so two rows with identical total_spend can land in different buckets purely based on row order.
If the business requires equal values to share a tier, NTILE is the wrong tool; you need a value-based approach instead. Interviewers plant this trap deliberately.
Deciles and Percentiles
The bucket count is just the number you pass. NTILE(10) gives deciles, NTILE(100) gives percentile bands. This is how analysts segment users into performance tiers or risk bands.
The output is the band number, so a value in NTILE(10) bucket 9 sits in the second-highest decile.
SELECT
user_id,
score,
NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM leaderboard;Partitioned Buckets
Add PARTITION BY to bucket within each group independently, for example quartiles of spend per region. Each region restarts at bucket 1.
This answers questions like "top-quartile customers within each region," where a region with low overall spend still has its own bucket 4.
SELECT
region,
customer_id,
total_spend,
NTILE(4) OVER (
PARTITION BY region
ORDER BY total_spend DESC
) AS regional_quartile
FROM customers;Filtering to a Specific Tier
You cannot put NTILE(...) directly in a WHERE clause; window functions are computed after WHERE. Wrap the query in a CTE or subquery, then filter on the bucket column.
This pattern, "give me the top quartile of spenders," is the most common way NTILE shows up in a real answer.
WITH q AS (
SELECT customer_id, total_spend,
NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customers
)
SELECT customer_id, total_spend
FROM q
WHERE quartile = 1;NTILE vs Value-Based Percentiles
NTILE buckets by equal row counts. If you instead need a true statistical percentile (the value at the 90th percentile), use PERCENTILE_CONT or PERCENTILE_DISC.
- NTILE(100): which percentile band a row falls in, by rank position.
- PERCENTILE_CONT(0.9): the actual value at the 90th percentile.
Knowing the distinction separates a confident answer from a guess.
When ORDER BY Matters
NTILE requires an ORDER BY inside OVER; the buckets are meaningless without a defined order. The direction sets which end is bucket 1.
If ties make assignment ambiguous and the exact bucket of a borderline row matters, add a tiebreaker column to the ordering for deterministic, reproducible results.
Labeling Buckets With Names
Raw bucket numbers (1, 2, 3, 4) are rarely the final deliverable. Analysts usually map them to business labels like Low, Medium, High, and Top with a CASE expression over the NTILE result.
Compute NTILE in a CTE, then translate the number in the outer query. This keeps the window logic clean and makes the output presentation-ready.
WITH q AS (
SELECT customer_id, total_spend,
NTILE(4) OVER (ORDER BY total_spend) AS bucket
FROM customers
)
SELECT customer_id, total_spend,
CASE bucket
WHEN 1 THEN 'Low'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'High'
WHEN 4 THEN 'Top'
END AS spend_tier
FROM q;Quick Check
Test the uneven-distribution rule.
Recap
NTILE distributes ordered rows into equal-count buckets:
NTILE(n)labels rows 1..n;NTILE(4)= quartiles,NTILE(10)= deciles.- It splits by row count, not value range, and gives extra rows to the earlier buckets.
- It does not keep tied values together, and cannot go directly in
WHERE. - For a true percentile value, reach for
PERCENTILE_CONT.
Next: pulling boundary values with FIRST_VALUE, LAST_VALUE, and frame edges.
Frequently asked questions
Is the “NTILE for Bucketing” lesson free?
Yes — the full text of “NTILE for Bucketing” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “NTILE for Bucketing”?
Splitting rows into quartiles and percentile bands. You practise SQL Interview Prep 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 Interview Prep?
No prior experience is required. SQL Interview Prep 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 “NTILE for Bucketing” 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 Interview Prep lesson?
Yes. Every SQL Interview Prep 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.