NTILE Function

Part of the Fundamentals section of Coddy's SQL journey — lesson 67 of 72.

NTILE(n) numbers the rows by splitting them into n approximately equal pieces. It is often used for performance enhancements - sending large amounts of data at once might be not a good idea, so this function allows to send smaller pieces at a time.

For example:

id	level
1	4
2	4
3	5
4	6
5	7
6	7

SELECT id, level, NTILE(3) OVER (ORDER BY level) as pieces
from table1

This will return:

id	level	pieces
1	4	1
2	4	1
3	5	2
4	6	2
5	7	3
6	7	3

We got 3 pieces: level 4 in piece 1, level 5 and level 6 in piece 2, and level 7 in piece 3.
When the number of rows isn't evenly divisible by n, NTILE distributes the rows as evenly as possible, with larger groups appearing first. For example, if you have 9 rows and NTILE(4), the distribution would be:

Group 1: 3 rows
Group 2: 2 rows
Group 3: 2 rows
Group 4: 2 rows

This ensures that no group differs by more than one row from any other group, and any extra rows are distributed to the lower-numbered groups first.

Challenge

Easy

Available tables and columns:
creatures: creature_name, preference, has_legs (contains 'YES' or 'NO')

In this challenge, we have creatures that want to find a significant other. We need to help them find their match.

Each creature has a preference that is represented as a number. If both creatures have a number that is close enough, they might be a good match.

Divide creatures into 3 groups based on their preference values, but only consider creatures that have legs (where has_legs = 'YES'). Return the creature_name, preference, and their group_number (1, 2, or 3). Order the results by preference in ascending order.

Cheat sheet

The NTILE(n) function splits rows into n approximately equal groups, numbering them sequentially:

SELECT column1, column2, NTILE(3) OVER (ORDER BY column2) as group_number
FROM table_name

When rows aren't evenly divisible by n, NTILE distributes rows as evenly as possible, with larger groups appearing first. For example, with 9 rows and NTILE(4):

Group 1: 3 rows
Group 2: 2 rows
Group 3: 2 rows
Group 4: 2 rows

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

NTILE Function

Cheat sheet

All lessons in Fundamentals