How does NTILE(4) distribute rows when the row count is not divisible by 4?

## What NTILE(n) does `NTILE(n)` takes the rows of a window partition in the window's `ORDER BY` sequence and tags each one with an integer from 1 to n, so that the buckets are as equal in **row count** as possible. It is the SQL spelling of "split this population into quartiles/deciles/percentiles". The argument n must be a positive integer. The key word is *count*. `NTILE` does not look at how far apart the values are; it counts rows and cuts. A bucket is a slice of the ordered sequence, not a range of values. ## The exact distribution rule Let R be the number of rows in the partition and n the number of buckets. Write R = q·n + r with 0 ≤ r < n. Then the first r buckets contain q+1 rows each, and the remaining n−r buckets contain q rows each. In other words, the extra rows go to the **lowest-numbered** buckets — the ones that come first in the window ordering. Examples: - R = 10, n = 4: q = 2, r = 2 → sizes 3, 3, 2, 2. - R = 12, n = 4: q = 3, r = 0 → sizes 3, 3, 3, 3. - R = 7, n = 3: q = 2, r = 1 → sizes 3, 2, 2. ## Fewer rows than buckets When R < n, q is 0 and r is R, so the first R buckets get one row each and the remaining buckets get zero. Three rows with `NTILE(5)` produce the bucket numbers 1, 2 and 3; buckets 4 and 5 exist only conceptually and never appear in the output. This is not an error, and it is a routine surprise when `NTILE(10)` is applied per partition and some partitions are small — a customer with four orders gets deciles 1 to 4 only. ## Ties straddle boundaries Because the cut is positional, `NTILE` gives no special treatment to rows that the `ORDER BY` cannot distinguish. If a boundary falls in the middle of a run of equal values, some of those rows land in bucket k and the rest in bucket k+1 — and which ones cross is exactly as arbitrary as `ROW_NUMBER()`'s tie handling. Adding a unique tiebreaker to the window `ORDER BY` makes the split reproducible across runs, but it does not make equal values share a bucket; nothing about `NTILE` can do that, because the bucket sizes are fixed by construction. That is the trade-off to state in an interview: `NTILE` guarantees near-equal bucket **sizes** and therefore cannot also guarantee that equal **values** share a bucket. If your requirement is the second one, `NTILE` is the wrong tool. ## Writing it ```sql SELECT customer_id, total_spend, NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile FROM customer_totals; ``` A `PARTITION BY` makes the bucketing independent per group, and the size rule then applies within each partition separately: ```sql SELECT region, customer_id, total_spend, NTILE(4) OVER (PARTITION BY region ORDER BY total_spend DESC) AS regional_quartile FROM customer_totals; ``` As with the other ranking functions, an `NTILE` result is determined by the partition and the window `ORDER BY` alone; there is no frame to specify. Some engines require an `ORDER BY` in the `OVER` clause for ranking functions while others accept its absence and then produce an arbitrary assignment — engines differ, so specify the ordering explicitly and the question never arises. ## Reading the output Bucket 1 is always the first slice in the window ordering. With `ORDER BY total_spend DESC`, bucket 1 is the highest spenders; with `ASC` it is the lowest. Reversing the sort silently inverts the meaning of every bucket label, which is a classic source of upside-down dashboards, so state the direction wherever the quartile is documented.