NTILE function

Lesson 10 of 13 in Coddy's SQL for advanced course.

NTILE(n) numbers the rows by splitting them into n 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.
If n in NTILE(n) is not dividable with the length of the table: Let's say the table has 9 rows and n is 4, it will put as much as it can in the last piece.

Challenge

Medium

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.

Find the best couples. Create a new column named couple_matcher that is numbered from one to the number of creatures divided by two where each couple should have the same number. Couples can only be formed if one creature has legs and the other does not.

Order the result by couple_matcher and creature_name in ascending order.

Try it yourself

All lessons in SQL for advanced

1Introdcution

Introduction Simplify queries, WITH keyword

4Summary

Final challenge #1

2Window Functions part 1

ROW_NUMBER function ORDER BY criterion PARTITION BY criterion LEAD & LAG functions Recap challenge #1 Recap challenge #2

3Window Functions part 2

RANK & DENSE_RANK functionsNTILE functionAggregation functions ROWS & RANGE criterion