NTILE OVER

Divides an ordered data set equally into the number of buckets specified by the argument value. Buckets are sequentially numbered 1 through the argument value. For more information on usage, please refer to Window Functions.

Syntax

NTILE( <value> ) OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ { ASC | DESC } ] )

Parameters

Parameter	Description	Supported input types
`<value>`	An integer expression used for the `NTILE()` function to specify the number of buckets for division.	`INTEGER`/`BIGINT`
`<partition_by>`	An expression used for the partition by clause.	Any
`<order_by>`	An expression used for the order by clause.	Any

Return Type

INTEGER/BIGINT If there is a remainder after dividing the rows in a partition by the argument value, it will result in buckets of different sizes. For example,

NTILE(2) over 5 rows will result in 2 buckets, the first with 3 rows, the second with 2
NTILE(3) over 5 rows results in 3 buckets, the first 2 with 2 rows each and the last with one.

Example

The example below divides students test results into three buckets depending on their score.

SELECT
	student_name,
	score,
	NTILE(3) OVER (ORDER BY score) AS bucket
FROM
	student_test_results
ORDER BY score;

Returns:

student_name	score	bucket
James	9	1
Emma	10	1
Harry	11	1
Liam	11	2
Ava	12	2
Charly	12	2
Noah	13	3
Olivia	13	3

NTH_VALUE OVER

PERCENT_RANK OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Example

​Syntax

​Parameters

​Return Type

​Example

Syntax

Parameters

Return Type

Example