NTILE for Bucketing
Splitting rows into quartiles and percentile bands.
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;