NTILE for distribution in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NTILE function do in SQL?

NTILE divides rows in a result set into a specified number of groups, called tiles, and assigns a tile number to each row to show which group it belongs to.

intermediate

How does NTILE handle rows when they can't be evenly divided into tiles?

NTILE distributes the extra rows one by one to the first tiles until all rows are assigned, so some tiles may have one more row than others.

beginner

Write a simple SQL query using NTILE to split 10 rows into 3 groups ordered by a column named 'score'.

SELECT score, NTILE(3) OVER (ORDER BY score) AS tile FROM your_table;

intermediate

Can NTILE be used without an ORDER BY clause inside the OVER() function? Why or why not?

No, NTILE requires an ORDER BY clause inside OVER() to define how rows are sorted before dividing into tiles. Without ordering, the distribution would be unpredictable.

beginner

What is a practical use case for NTILE in real life?

NTILE can be used to divide students into performance groups (like top 25%, middle 50%, bottom 25%) based on their test scores for targeted support.

What does NTILE(4) do when applied to 10 rows?

A Divides rows into 10 groups, one row each

B Divides rows into 4 groups, each with exactly 2 rows

C Divides rows into 4 groups, some groups having 3 rows and some 2 rows

D Divides rows into 4 groups, each with exactly 4 rows

NTILE(4) divides 10 rows into 4 groups. Since 10/4 is 2.5, two groups will have 3 rows and two groups will have 2 rows.

Which clause is mandatory inside the OVER() clause when using NTILE?

A PARTITION BY

B ORDER BY

C GROUP BY

D WHERE

NTILE requires ORDER BY inside OVER() to sort rows before dividing them into tiles.

If you want to split data into 5 equal groups, which NTILE argument should you use?

A NTILE(5)

B NTILE(10)

C NTILE(1)

D NTILE(0)

The argument to NTILE is the number of groups you want, so NTILE(5) splits data into 5 groups.

What happens if the number of rows is less than the NTILE number?

A All rows go to the first tile

B An error occurs

C Rows are duplicated to fill tiles

D Some tiles will be empty

If rows are fewer than tiles, some tiles will have no rows assigned.

Which SQL clause can be combined with NTILE to restart numbering for each group?

A PARTITION BY

B WHERE

C GROUP BY

D HAVING

PARTITION BY inside OVER() restarts NTILE numbering for each group.

Explain how NTILE distributes rows into groups when the total number of rows is not divisible evenly by the number of tiles.

Think about how leftover rows are assigned.

Describe a real-world scenario where using NTILE would help organize data effectively.

Consider grouping people or items based on scores or values.