NTILE

NTILE(n) is a window function that divides the rows in a partition into n roughly equal-sized buckets and returns the bucket number for each row.

Overview

NTILE(n) splits an ordered set of rows into n groups of as-equal-as-possible size and labels each row with its group number, from 1 to n. It's the SQL building block for quartiles, deciles, and percentile-style bucketing.

Copy code
SELECT
  customer_id,
  lifetime_value,
  NTILE(4) OVER (ORDER BY lifetime_value DESC) AS value_quartile
FROM customers;

If the row count doesn't divide evenly by n, DuckDB and standard SQL both distribute the remainder to the earliest buckets, so some early groups may have one more row than later ones.

Common uses

Quartile/decile segmentation: NTILE(4) for customer value quartiles, NTILE(10) for deciles.
A/B or cohort bucketing: assign rows to a fixed number of experiment groups deterministically.
Load balancing: split a large ordered dataset into n roughly even chunks for parallel processing.

Copy code
SELECT value_quartile, COUNT(*), AVG(lifetime_value)
FROM (
  SELECT lifetime_value, NTILE(4) OVER (ORDER BY lifetime_value DESC) AS value_quartile
  FROM customers
)
GROUP BY ALL
ORDER BY ALL;

DuckDB specifics

DuckDB implements NTILE(n) as a standard window function, usable with PARTITION BY to bucket independently within each group (e.g., quartiles computed separately per region). DuckDB's GROUP BY ALL and ORDER BY ALL shortcuts pair well with NTILE output when summarizing bucket statistics, as shown above, avoiding the need to repeat column lists.

Related terms

PARTITION BY →

PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.

ROW_NUMBER →

ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.

row group →

A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…

OVER clause →

The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.

S3 bucket →

An S3 bucket is a fundamental storage container in Amazon Web Services' Simple Storage Service (S3).

Window frame →

A window frame is the subset of rows within a window function's partition that a calculation is applied to, defined with ROWS, RANGE, or GROUPS relative to the current row.

FAQS

The remainder rows are distributed one at a time to the earliest buckets, so those buckets end up with one extra row each compared to later buckets.

It's related but not identical. NTILE assigns each row to one of n discrete groups based on row order, while percentile functions like PERCENT_RANK() or CUME_DIST() return a continuous fractional rank for each row.