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

Concept Flow - NTILE for distribution

Start with dataset

↓

Order rows by specified column

↓

Divide rows into N buckets

↓

Assign bucket number to each row

↓

Return rows with bucket number

↓

End

NTILE divides ordered rows into a specified number of groups (buckets) and assigns each row a bucket number.

Execution Sample

PostgreSQL

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

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

Execution Table

Row	id	score	Ordered Position	Bucket Number	Action
1	101	95	1	1	First row, assigned to bucket 1
2	102	90	2	1	Second row, bucket 1
3	103	85	3	2	Third row, bucket 2
4	104	80	4	2	Fourth row, bucket 2
5	105	75	5	3	Fifth row, bucket 3
6	106	70	6	3	Sixth row, bucket 3
7	107	65	7	3	Seventh row, bucket 3
Exit					All rows assigned to one of 3 buckets

💡 All 7 rows are divided into 3 buckets; buckets 1 and 2 have 2 rows each, bucket 3 has 3 rows.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	After Row 7	Final
Bucket Number	N/A	1	1	2	2	3	3	3	Final bucket assignments per row

Key Moments - 2 Insights

Why do some buckets have more rows than others?

Because the total number of rows (7) is not divisible evenly by the number of buckets (3), some buckets get one extra row. See execution_table rows 5-7 where bucket 3 has 3 rows.

How does NTILE decide which rows go to which bucket?

NTILE orders rows by the specified column (score DESC here), then divides them into buckets in order. The first rows get bucket 1, next bucket 2, and so on, as shown in the Ordered Position and Bucket Number columns.

Visual Quiz - 3 Questions

Look at the execution_table, what bucket number is assigned to the row with id 103?

A 1

B 3

C 2

D None

💡 Hint

Check the row where id is 103 in the execution_table and see the Bucket Number column.

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

A Row 3

B Row 2

C Row 4

D Row 5

💡 Hint

Look at the Bucket Number column in execution_table and find where it changes from 1 to 2.

If we change NTILE(3) to NTILE(2), how many buckets will the rows be divided into?

A 3

B 2

C 7

D 1

💡 Hint

NTILE(N) always divides rows into N buckets, so changing 3 to 2 means 2 buckets.

Concept Snapshot

NTILE(n) divides ordered rows into n groups (buckets).
Rows are assigned bucket numbers from 1 to n.
If rows don't divide evenly, some buckets get one more row.
Use OVER(ORDER BY column) to specify order.
Useful for ranking or distributing data evenly.

Full Transcript

NTILE is a function in SQL that splits rows into a specified number of groups called buckets. First, it orders the rows by a chosen column. Then it divides the rows into equal or nearly equal buckets. Each row gets a bucket number from 1 up to the number of buckets. If the total rows don't divide evenly, some buckets have one extra row. For example, with 7 rows and NTILE(3), buckets 1 and 2 get 2 rows each, and bucket 3 gets 3 rows. This helps distribute data evenly for analysis or reporting.