NTILE for distribution in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to divide rows into 4 groups using NTILE.

SQL

SELECT employee_id, salary, NTILE([1]) OVER (ORDER BY salary DESC) AS salary_group FROM employees;

Drag options to blanks, or click blank then click option'

A3

B5

C2

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number other than 4 when 4 groups are needed.

Forgetting to specify the number inside NTILE.

The NTILE function divides rows into the specified number of groups. Here, 4 groups are requested.

2fill in blank

medium

Complete the code to assign quartiles based on sales amount.

SQL

SELECT customer_id, sales_amount, NTILE([1]) OVER (ORDER BY sales_amount) AS quartile FROM sales;

Drag options to blanks, or click blank then click option'

A4

B2

C3

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using NTILE with a number other than 4 for quartiles.

Ordering by wrong column.

Quartiles divide data into 4 equal parts, so NTILE(4) is used.

3fill in blank

hard

Fix the error in the NTILE function to split data into 3 groups.

SQL

SELECT product_id, price, NTILE([1]) OVER (ORDER BY price DESC) AS price_group FROM products;

Drag options to blanks, or click blank then click option'

A4

B0

C3

D-1

Attempts:

3 left

💡 Hint

Common Mistakes

Using zero or negative numbers in NTILE.

Using a number different from 3 when 3 groups are needed.

NTILE requires a positive integer greater than zero. 3 groups means NTILE(3).

4fill in blank

hard

Fill both blanks to create 5 groups ordered by score descending.

SQL

SELECT student_id, score, NTILE([1]) OVER (ORDER BY score [2]) AS score_group FROM students;

Drag options to blanks, or click blank then click option'

A5

BASC

CDESC

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using ASC instead of DESC for descending order.

Using wrong number of groups.

NTILE(5) creates 5 groups. Ordering by score descending uses DESC.

5fill in blank

hard

Fill all three blanks to assign NTILE groups and filter for group 1 only.

SQL

WITH ranked AS (SELECT order_id, amount, NTILE([1]) OVER (ORDER BY amount [2]) AS group_num FROM orders) SELECT * FROM ranked WHERE group_num [3] 1;

Drag options to blanks, or click blank then click option'

A4

BDESC

C=

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operator in WHERE clause.

Using ascending order when descending is needed.

NTILE(4) divides into 4 groups, ordered descending by amount. Filtering for group_num = 1 selects the first group.

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