By Prakhar Shrivastava · October 14, 2026

NTILE balances the number of rows, not the meaning of equal values. A bucket boundary can divide customers whose spend is identical. A unique tie-breaker makes that assignment reproducible; it does not make the customers different in value. If equal spend must receive the same segment, use a rule based on spend rather than assuming NTILE preserves ties.

A US and UK segmentation example

Suppose a fictional business wants three customer groups in each market. The input has exactly one row per customer for the same completed reporting period. All amounts are already expressed in USD; this example performs no currency conversion. Spend is known and nonnegative. Returns, missing amounts, and inactive customers require separate documented inclusion rules in a production report.

The six US customers spent $100, $100, $100, $100, $50, and $10. Three equally sized row groups contain two customers each, so the four $100 customers cannot all remain together. The four UK customers spent $200, $100, $100, and $20. Three groups cannot have exactly equal sizes there either.

Run the complete query

WITH customers(market, customer_id, spend_usd) AS (
 VALUES ('US',1,100),('US',2,100),('US',3,100),
        ('US',4,100),('US',5,50),('US',6,10),
        ('UK',7,200),('UK',8,100),('UK',9,100),('UK',10,20)
)
SELECT market, customer_id, spend_usd,
 NTILE(3) OVER (
   PARTITION BY market ORDER BY spend_usd DESC, customer_id
 ) AS row_bucket,
 DENSE_RANK() OVER (
   PARTITION BY market ORDER BY spend_usd DESC
 ) AS spend_level
FROM customers
ORDER BY market, spend_usd DESC, customer_id;

The customer ID is a stable, unique tie-breaker for NTILE. The DENSE_RANK ordering deliberately excludes it, because the second calculation asks which distinct spend level a customer occupies.

What should the output show?

In US customer-ID order, row buckets are 1, 1, 2, 2, 3, 3. Customers 1 and 2 are in bucket 1, while customers 3 and 4 are in bucket 2 despite all four spending $100. Spend levels are 1, 1, 1, 1, 2, 3.

In UK customer-ID order, row buckets are 1, 1, 2, 3. Customers 8 and 9 both spent $100 but land in different buckets. Spend levels are 1, 2, 2, 3. Four assertions checking these complete sequences passed in SQLite 3.50.4. The query uses standard window-function syntax; verify behavior in your deployed database before using it for a production decision.

Which rule matches the business question?

Use NTILE when the operational need is approximately balanced workloads, such as splitting a customer list into three review batches. Call them batches, and disclose the tie-breaker. The number of customers is being balanced; neither total spend nor review effort is guaranteed to balance.

Use explicit value thresholds when the labels describe customer value. For example, a fictional policy of at least $100 for high spend, at least $50 for medium spend, and otherwise low spend places all four US $100 customers together. Those cutoffs are illustrative business choices, not statistically estimated terciles or recommended commercial thresholds. Document the period and currency alongside them.

DENSE_RANK can identify distinct spend levels while preserving equal values, but it is not a drop-in replacement for three buckets. This fixture happens to have three distinct levels in each market. With 100 different spend values, it can produce 100 ranks. A percentile-based policy also needs an explicit boundary rule for ties and may produce unequal group sizes.

Why can assignments change next month?

NTILE depends on the other rows in the partition. Adding or removing customers can move a bucket boundary even when an existing customer’s spend is unchanged. A deterministic customer-ID tie-breaker prevents arbitrary ordering of equal spend in the same input; it does not freeze membership when the input changes.

Keep a dated snapshot of the eligible population, reporting period, sorting rule, and resulting assignment when operational reproducibility matters. Compare customers under the same eligibility policy before describing movement as improved or reduced customer value.

Check the grain before ranking

If the source contains orders, first aggregate to one customer per market for the agreed period. Otherwise NTILE balances order rows and repeat purchasers receive multiple positions. Validate the uniqueness of the market/customer key and audit missing spend separately; do not silently turn unknown spend into zero.

For ranking positions and tie rules, continue with the window-function practice guide. If joins inflate your input, use the duplicate-join revenue example. For unknown amounts, see NULL versus zero in SQL.

Primary reference: PostgreSQL window functions, including NTILE and DENSE_RANK. The central choice is the unit you want to group: rows or values.