sqlindex

Window functions

Split rows into equal buckets (NTILE quartiles & deciles)

2 min · updated August 21, 2026

NTILE(n) sorts the rows and splits them into n as-equal-as-possible buckets, numbered 1..n. Great for quartiles (4), deciles (10), or percentile bands.

-- Assign each customer to a spending quartile (1 = lowest, 4 = highest)
SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM customer_totals;

Buckets within each group — add PARTITION BY:

SELECT
  region,
  customer_id,
  total_spent,
  NTILE(10) OVER (PARTITION BY region ORDER BY total_spent) AS decile
FROM customer_totals;

NTILE vs percentiles. NTILE puts an equal number of rows in each bucket; it does not cut on round value boundaries. If you need the actual cutoff values (e.g. the 90th-percentile amount), use PERCENTILE_CONT/PERCENTILE_DISC instead — see the median and percentiles recipe.

Supported in all six dialects (MySQL 8+, SQLite 3.25+). When row counts don’t divide evenly, the earlier buckets get one extra row.

← All recipes