Skip to main content

NTILE

Divides the partition into N groups as evenly as possible and assigns an integer between 1 and N to each group, in the order defined by the ORDER BY clause, or in arbitrary order otherwise. If necessary, larger groups occur first.

See also: [SELECT] [RANK]

Syntax​

SELECT NTILE(N) OVER ([PARTITION BY column] ORDER BY column [ASC|DESC])
FROM <multipartIdentifier>;

Arguments​

N A positive integer specifying the number of groups (tiles) to divide the partition into.

PARTITION BY column Optional. Divides the result set into partitions. The NTILE function is applied to each partition separately.

ORDER BY column Specifies the order in which rows are assigned to tiles.

Return Value(s)​

Returns an integer between 1 and N indicating which tile (group) the current row belongs to.

Examples​

Group contributors into quartiles by contribution level​

-- Group contributors into quartiles by contribution level
SELECT
    login,
    contributions,
    NTILE(4) OVER (ORDER BY contributions DESC) as contribution_quartile,
    CASE NTILE(4) OVER (ORDER BY contributions DESC)
        WHEN 1 THEN 'Top Contributors'
        WHEN 2 THEN 'Active Contributors'
        WHEN 3 THEN 'Moderate Contributors'
        WHEN 4 THEN 'Occasional Contributors'
    END as contributor_tier
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';
tip

Use NTILE to create segments or tiers in your data. Common use cases include dividing data into quartiles (N=4), deciles (N=10), or percentiles (N=100).

For more information, see https://sqlite.org/windowfunctions.html#built-in_window_functions.