NTILE
Split ordered rows into a fixed number of buckets — quartiles, deciles, cohorts — by row count, not by value.
Syntax
NTILE(bucket_count) OVER (
[PARTITION BY partition_expression, ...]
ORDER BY sort_expression [ASC | DESC], ...
)| Parameter | Type | Notes |
|---|---|---|
| bucket_count* | integer | How many groups to split the rows into. |
| ORDER BY* | expression [ASC|DESC], … | The ordering that determines which rows land in bucket 1 vs the last bucket. |
What it does
NTILE(n) distributes ordered rows into n groups as evenly as possible and
returns each row's bucket number (1 to n). Combine it with GROUP BY to
bucket an aggregate, like a per-customer total:
SQL playground
Loading editor…
⌘/Ctrl + Enter
Quartile 1 is the top-spending 25% of customers by this ordering.
Uneven buckets
5 customers into 4 buckets can't divide evenly — earlier buckets absorb the extra rows:
SQL playground
Loading editor…
⌘/Ctrl + Enter
7 employees into 3 buckets gives sizes 3/2/2, not 3/3/1 — the first bucket(s) get the remainder, one row at a time, front-loaded.
Gotchas
- Splits by row count, not by value. Two rows with the same value can land
in different buckets if the split happens to fall between them —
NTILEdoesn't know or care that they're tied. - Bucket boundaries shift when the data does. Add a row, delete a row, or filter differently, and the same customer can land in a different quartile next time — buckets aren't stable identifiers.
- For a fixed value threshold ("customers who spent over $1,000"), use
CASE/WHERE, notNTILE—NTILEonly knows about rank position, not the actual value gap between buckets.
- ROW_NUMBER — the plain per-row counter NTILE is built from.
- Top N per group — when you want a fixed cutoff instead of even buckets.
- Window frames — NTILE ignores the frame clause; it only reads PARTITION BY/ORDER BY.
Safety check before shipping
- confirm you want equal-sized groups rather than fixed value cutoffs
- decide how ties on a bucket boundary should be handled — add a tiebreaker to ORDER BY
- document that bucket membership changes when the underlying data changes
Was this page helpful?