NTILE(10) puts identical spend values in different deciles and boundaries move as data grows - how do you get stable segments?

## Diagnose the mismatch first The complaint hides two distinct behaviours, and they have different answers. **Behaviour one: identical values in different buckets.** `NTILE(n)` divides by row position. With R rows it fills the first R mod n buckets with one extra row each and the rest with the base count; a boundary therefore falls at a fixed *position*, regardless of whether the values on either side of it are equal. If forty customers all spent exactly 100 and a decile boundary lands inside that block, some of them are labelled 3 and the others 4. No option to `NTILE` changes this — equal bucket sizes and "peers stay together" are mutually exclusive guarantees. **Behaviour two: drifting boundaries.** The cut-points are defined by the population, not by the metric. Onboard a cohort of low-spending customers and everyone else shifts up a decile without transacting. That is inherent to any equal-count measure and is not a defect either — but it is fatal if the segment drives entitlements, pricing or an SLA that a customer can read. ## Decide what the segment means Ask which sentence the business actually wants to publish. - *"You are in our top ten percent of customers."* This is a **relative** statement about the population. `NTILE(10)` is the correct implementation, drift and all, because the whole point is that the cohort is one tenth of the base at all times. - *"Customers spending over 5,000 are Platinum."* This is an **absolute** statement about the value. It must not depend on who else exists, so it cannot be computed by an equal-count function at all. Most "our deciles are unstable" complaints are the second requirement implemented with the first tool. ## Fixing the relative case Keep `NTILE(10)`, and make it reproducible and explicable: ```sql SELECT customer_id, total_spend, NTILE(10) OVER (ORDER BY total_spend DESC, customer_id) AS spend_decile FROM customer_totals; ``` The `customer_id` tiebreaker makes the window ordering total, so the same data yields the same labels on every run — it removes run-to-run churn, though it still cannot keep equal values together. Alongside the label, publish the boundary values (the minimum and maximum spend in each decile) so a reader can see where the cuts landed and why they moved. And expect small partitions to behave oddly: `NTILE(10)` over four rows yields deciles 1 to 4 only. If peers being split is the specific objection while the equal-count spirit is fine, rank the **distinct values** rather than the rows — bucket on `DENSE_RANK()` over the distinct spend levels — and accept that the resulting groups are no longer equal in size. You are trading one guarantee for the other; there is no way to hold both. ## Fixing the absolute case Compute the cut-points once, freeze them, and apply them as value tests: ```sql SELECT customer_id, total_spend, CASE WHEN total_spend >= 5000 THEN 'platinum' WHEN total_spend >= 1000 THEN 'gold' WHEN total_spend >= 200 THEN 'silver' ELSE 'bronze' END AS segment FROM customer_totals; ``` The thresholds can still be *derived* from data — take a reference snapshot, read the decile boundaries off it with `CUME_DIST()` or `PERCENT_RANK()`, or with your engine's ordered-set aggregates such as `PERCENTILE_CONT` where they are available — but once chosen they become constants stored in a small reference table and reviewed on a schedule. That gives three properties `NTILE` cannot: equal values always share a segment, a customer's segment changes only when their own spend changes, and yesterday's report reproduces exactly. ## The reporting discipline around either choice Whichever you pick, write it down in the metric definition: the sort direction (bucket 1 is the top only under `DESC`), whether the segment is relative or absolute, the refresh cadence of the thresholds, and what happens to a customer sitting exactly on a boundary. Segment definitions leak into emails, contracts and comparisons across time; the most expensive failure here is not the SQL, it is two dashboards using the word "decile" for two different computations.