NTILE for distribution in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding NTILE distribution with 3 buckets

Given the table Sales with a column amount, what will be the NTILE value for the row with amount = 50 when using NTILE(3) OVER (ORDER BY amount)?

SQL

CREATE TABLE Sales(amount INT);
INSERT INTO Sales VALUES (10), (20), (30), (40), (50), (60), (70);

SELECT amount, NTILE(3) OVER (ORDER BY amount) AS bucket FROM Sales ORDER BY amount;

A 1

B 2

C 3

D Error

💡 Hint

NTILE divides rows into equal groups ordered by the column.

The 7 rows are divided into 3 buckets: first 3 rows in bucket 1, next 2 rows in bucket 2, last 2 rows in bucket 3. The amount 50 is the 5th row, so it falls in bucket 2.

🧠 Conceptual

intermediate

What does NTILE do in SQL?

Which of the following best describes what the SQL function NTILE(n) does when used as a window function?

A Divides rows into <code>n</code> groups with equal number of rows as much as possible, ordered by the specified column.

B Calculates the cumulative sum of values divided by <code>n</code>.

C Returns the rank of each row within its partition.

D Counts the total number of rows in the table.

💡 Hint

Think about how NTILE splits rows into groups.

NTILE(n) splits the ordered rows into n groups as evenly as possible, assigning a group number to each row.

📝 Syntax

advanced

Identify the syntax error in NTILE usage

Which option contains a syntax error when using NTILE in a SQL query?

SQL

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

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

B SELECT employee_id, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM Employees;

C SELECT employee_id, NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS quartile FROM Employees;

D SELECT employee_id, NTILE(4) AS quartile FROM Employees ORDER BY salary;

💡 Hint

NTILE requires an OVER clause with ORDER BY.

Option D misses the OVER clause, which is mandatory for NTILE as a window function.

❓ query_result

advanced

NTILE with ties in ordering column

Given the table Scores with values (100, 90, 90, 80, 70) in column score, what is the NTILE(2) value for the rows with score 90 when using NTILE(2) OVER (ORDER BY score DESC)?

SQL

CREATE TABLE Scores(score INT);
INSERT INTO Scores VALUES (100), (90), (90), (80), (70);

SELECT score, NTILE(2) OVER (ORDER BY score DESC) AS bucket FROM Scores ORDER BY score DESC;

A Error

B 2

C 1

D Both 1 and 2

💡 Hint

NTILE assigns buckets based on row position, ties do not get same bucket necessarily.

Rows are ordered by score descending: 100(1), 90(2), 90(3), 80(4), 70(5). NTILE(2) divides 5 rows into 3 and 2 rows. First 3 rows get bucket 1, last 2 get bucket 2. The two 90 scores are in positions 2 and 3, both in bucket 1.

❓ optimization

expert

Optimizing NTILE on large datasets

You have a large table Transactions with millions of rows. You want to assign quartiles using NTILE(4) OVER (ORDER BY amount). Which approach will improve performance the most?

A Create an index on the <code>amount</code> column before running the NTILE query.

B Use a subquery to pre-sort the data and then apply NTILE on the result.

C Add a WHERE clause filtering only the top 1000 rows before applying NTILE.

D Run NTILE without any ORDER BY clause to avoid sorting overhead.

💡 Hint

Think about how sorting affects window functions.

NTILE requires sorting by the ORDER BY column. An index on that column speeds up sorting and improves query performance.

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