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.

Practice

1.

What does the SQL function NTILE(n) do when applied to a dataset?

easy

A. It filters rows based on a condition.

B. It sorts the dataset in ascending order.

C. It counts the total number of rows in the dataset.

D. It divides the dataset into n groups with roughly equal number of rows.

Solution

1. Step 1: Understand the purpose of NTILE

   NTILE divides rows into a specified number of groups, distributing rows as evenly as possible.

2. Step 2: Compare options with NTILE behavior

   Only It divides the dataset into n groups with roughly equal number of rows. correctly describes this grouping behavior; others describe different SQL functions.

3. Final Answer:

   It divides the dataset into n groups with roughly equal number of rows. -> Option D

4. Quick Check:

   NTILE splits data into equal groups = It divides the dataset into n groups with roughly equal number of rows. [OK]

Hint: NTILE splits rows into equal groups by number [OK]

Common Mistakes:

• Confusing NTILE with ORDER BY sorting
• Thinking NTILE counts rows
• Assuming NTILE filters rows

2.

Which of the following is the correct syntax to use NTILE to split rows into 4 groups ordered by salary?

SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

easy

A. SELECT employee_id, salary, NTILE(4) ORDER BY salary AS quartile FROM employees;

B. SELECT employee_id, salary, NTILE(4) BY salary AS quartile FROM employees;

C. SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

D. SELECT employee_id, salary, NTILE(4) PARTITION BY salary AS quartile FROM employees;

Solution

1. Step 1: Recall NTILE syntax with window functions

   NTILE requires OVER() with ORDER BY inside parentheses to define ordering.

2. Step 2: Check each option's syntax

   Only SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; uses correct syntax: NTILE(4) OVER (ORDER BY salary). Others misuse BY, ORDER BY, or PARTITION BY incorrectly.

3. Final Answer:

   SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; -> Option C

4. Quick Check:

   NTILE needs OVER(ORDER BY) syntax = SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; [OK]

Hint: NTILE always needs OVER(ORDER BY column) [OK]

Common Mistakes:

• Omitting OVER() clause
• Using BY instead of OVER(ORDER BY)
• Confusing PARTITION BY with ORDER BY

3.

Given the table sales with amount values: 10, 20, 30, 40, 50, what is the ntile(3) value for the row with amount = 40 when ordered by amount?

SELECT amount, NTILE(3) OVER (ORDER BY amount) AS tile FROM sales;

medium

A. 2

B. 1

C. 3

D. 4

Solution

1. Step 1: Order amounts and assign NTILE(3)

   Sorted amounts: 10, 20, 30, 40, 50. NTILE(3) splits 5 rows into 3 groups: first 2 rows in tile 1, next 2 in tile 2, last 1 in tile 3.

2. Step 2: Find tile for amount 40

   Amount 40 is the 4th row, which falls in tile 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Row 4 in 3 groups = tile 2 [OK]

Hint: Divide rows evenly; count position to find tile [OK]

Common Mistakes:

• Assuming equal group sizes always
• Assigning tile 3 to amount 40 incorrectly
• Confusing row position with tile number

4.

Identify the error in this query using NTILE:

SELECT id, NTILE(0) OVER (ORDER BY score) AS group_num FROM players;

medium

A. NTILE requires PARTITION BY clause.

B. NTILE cannot have 0 as argument; it must be a positive integer.

C. ORDER BY clause is missing inside OVER().

D. The alias group_num is not allowed.

Solution

1. Step 1: Check NTILE argument validity

   NTILE requires a positive integer greater than zero to define groups.

2. Step 2: Analyze the query

   NTILE(0) is invalid and causes an error; ORDER BY is present, PARTITION BY is optional, alias is valid.

3. Final Answer:

   NTILE cannot have 0 as argument; it must be a positive integer. -> Option B

4. Quick Check:

   NTILE argument > 0 required = NTILE cannot have 0 as argument; it must be a positive integer. [OK]

Hint: NTILE argument must be positive integer > 0 [OK]

Common Mistakes:

• Using zero or negative numbers in NTILE
• Thinking PARTITION BY is mandatory
• Confusing alias rules

5.

You want to split 10 employees into 4 groups using NTILE(4) ordered by experience_years. How many employees will be in the third group?

hard

A. 2

B. 3

C. 4

D. 1

Solution

1. Step 1: Calculate base group size

   10 employees divided into 4 groups means each group gets at least 2 employees (since 10/4 = 2.5).

2. Step 2: Distribute extra employees

   Since 10 is not divisible by 4, the first 2 groups get 3 employees each, and the last 2 groups get 2 employees each. The third group is the first group with 2 employees.

3. Step 3: Confirm group sizes

   Groups: 1=3, 2=3, 3=2, 4=2 employees.

4. Final Answer:

   2 -> Option A

5. Quick Check:

   10 rows / 4 groups = 2 or 3 per group; third group has 2 [OK]

Hint: Divide rows, extra go to earlier groups [OK]

Common Mistakes:

• Assuming all groups have equal size always
• Assigning 3 employees to third group incorrectly
• Ignoring leftover rows distribution