NTILE

NTILE(n) is a window function that divides the rows of a partition into n roughly equal groups, called buckets or tiles, and labels every row with the bucket number it falls into (from 1 to n). It is the SQL tool behind quartiles, deciles, percentiles, and any other "split my data into N equal-sized chunks" analysis.

SQL

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_totals;

NTILE(4) here splits customers into four groups ordered by how much they spent, with quartile 1 containing the biggest spenders. As with other window functions, NTILE accepts an optional PARTITION BY to compute buckets independently within each partition — for example, ranking customers into quartiles separately per country.

Worked example: spending deciles

Given a table of ten customers and their yearly spend:

SQL

customer_id | total_spent
------------+------------
1           | 9800
2           | 9200
3           | 8700
4           | 7600
5           | 6100
6           | 5400
7           | 4300
8           | 3100
9           | 1800
10          | 900

SQL

SELECT
  customer_id,
  total_spent,
  NTILE(5) OVER (ORDER BY total_spent DESC) AS spending_bucket
FROM customer_totals;

customer_id | total_spent | spending_bucket
------------+-------------+----------------
1           | 9800        | 1
2           | 9200        | 1
3           | 8700        | 2
4           | 7600        | 2
5           | 6100        | 3
6           | 5400        | 3
7           | 4300        | 4
8           | 3100        | 4
9           | 1800        | 5
10          | 900         | 5

With 10 customers split into 5 buckets, each bucket gets exactly 2 rows. Bucket 1 holds the top-spending pair, bucket 5 the lowest-spending pair — a clean way to build a top-20%-of-customers segment for a marketing campaign, or to feed a decile/quartile analysis into a report.

Use case: customer spending quartiles

A common business question is "who are our top 25% of customers by spend?" NTILE(4) answers this directly — filter to spending_quartile = 1 to isolate that top group without hand-writing percentile math:

SQL

WITH quartiles AS (
  SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
  FROM customer_totals
)
SELECT customer_id, total_spent
FROM quartiles
WHERE spending_quartile = 1;

Note

When the number of rows does not divide evenly by n, NTILE does not error or leave a partial, unbalanced bucket at the end. Instead it distributes the remainder one extra row at a time to the earliest buckets. For example, 10 rows split with NTILE(3) produces buckets of size 4, 3, 3 — the first bucket absorbs the extra row, not the last one. Because of this, don't assume every bucket in your results has an identical row count when the row total isn't a clean multiple of n.

Tip

NTILE relies entirely on ORDER BY to decide which rows land in the earlier vs. later buckets — it says nothing about the actual values within a bucket being close together (unlike, say, manually bucketing by fixed value ranges). Two rows with very different values can still end up in the same tile if they happen to be adjacent in the ordering.

NTILE vs RANK-family functions

ROW_NUMBER, RANK, and DENSE_RANK all describe a row's position relative to others one row at a time. NTILE instead describes which of n fixed-size groups a row belongs to — it is a bucketing tool, not a positional ranking tool. Use RANK/DENSE_RANK when you care about exact standing; use NTILE when you care about which segment (top 10%, middle 50%, bottom quartile, etc.) a row falls into.