What does NTILE do and where is it used?
NTILE(n) divides the ordered rows into n roughly equal buckets and returns the bucket number for each row. It is used for quartiles, percentiles and splitting data into groups.
Explain it simply
Line up the class by marks and split it into four equal queues: top quarter, second quarter and so on. NTILE(4) stamps each student with the queue number 1 to 4.
Example
Eight students ranked by marks:
SELECT name, marks,
NTILE(4) OVER (ORDER BY marks DESC) AS quartile
FROM students;With 8 rows, each bucket has 2 rows: the top two get 1, the next two get 2, and so on.
If rows do not divide evenly, earlier buckets get one extra row. For 10 rows and NTILE(4), the bucket sizes are 3, 3, 2, 2.
Typical uses:
- Top 25 percent customers by spend.
- Splitting users into A/B test groups.
- Quick percentile bands for salaries.
NTILE only balances row counts, not values. Two equal marks can land in different buckets, since ties are not kept together. For tie-aware percentages use PERCENT_RANK or CUME_DIST.
What to say in the interview
"NTILE(n) assigns bucket numbers 1 to n over the ordered window, with sizes differing by at most one row. It splits by row count and ignores ties."
Common mistakes
- Assuming equal values always share a bucket.