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.