NTILE for distribution in SQL - Step-by-Step Execution

Concept Flow - NTILE for distribution

Start with dataset

↓

Order rows by column

↓

Divide rows into N buckets

↓

Assign bucket number to each row

↓

Output rows with bucket number

↓

End

NTILE divides ordered rows into a specified number of groups and assigns each row a group number.

Execution Sample

SQL

SELECT name, score, NTILE(3) OVER (ORDER BY score DESC) AS bucket
FROM students;

This query divides students into 3 groups based on their scores, assigning each a bucket number.

Execution Table

Step	Row (name, score)	Ordered Position	NTILE(3) Bucket	Explanation
1	('Alice', 95)	1	1	Highest score, first bucket
2	('Bob', 90)	2	1	Second highest, still bucket 1
3	('Charlie', 85)	3	2	Third highest, starts bucket 2
4	('David', 80)	4	2	Fourth highest, bucket 2
5	('Eve', 75)	5	3	Lowest score, bucket 3
6	End	-	-	All rows assigned to one of 3 buckets

💡 All rows assigned to buckets 1 to 3 based on ordered position.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
Ordered Position	-	1	2	3	4	5	5
NTILE Bucket	-	1	1	2	2	3	3

Key Moments - 2 Insights

Why do some buckets have more rows than others?

Because the total rows may not divide evenly by the number of buckets, some buckets get one extra row as shown in execution_table rows 1-5.

How does NTILE assign bucket numbers?

NTILE orders rows first, then divides them into buckets in order, assigning bucket numbers starting from 1 as seen in the 'NTILE Bucket' column.

Visual Quiz - 3 Questions

Look at the execution_table, what bucket is assigned to 'Charlie' at step 3?

A 2

B 3

C 1

D None

💡 Hint

Check the 'NTILE(3) Bucket' column at step 3 in execution_table.

At which step does the bucket number change from 1 to 2?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'NTILE(3) Bucket' values in execution_table rows 2 and 3.

If we change NTILE(3) to NTILE(2), how many buckets will be assigned?

A 1

B 3

C 2

D 5

💡 Hint

NTILE(N) always creates N buckets, so NTILE(2) creates 2 buckets.

Concept Snapshot

NTILE(N) divides ordered rows into N groups.
Rows are ordered by specified column(s).
Each row gets a bucket number from 1 to N.
Buckets may have uneven sizes if rows don't divide evenly.
Useful for distributing data into equal parts.

Full Transcript

NTILE is a SQL function that splits ordered rows into a set number of groups called buckets. First, the rows are sorted by a column you choose. Then, NTILE divides these rows into N buckets, assigning each row a bucket number from 1 to N. If the total rows don't divide evenly, some buckets get one more row. For example, with 5 rows and NTILE(3), the first bucket gets 2 rows, the second bucket 2 rows, and the last bucket 1 row. This helps distribute data evenly for analysis or reporting.