NTILE for distribution in SQL - Mini Project: Build & Apply

Distributing Sales Data into Quartiles Using NTILE

📖 Scenario: You work for a retail company that wants to analyze sales performance by dividing salespeople into four groups based on their total sales. This helps identify top performers and those who may need support.

🎯 Goal: Create a SQL query that uses the NTILE function to split salespeople into four groups (quartiles) based on their total sales.

📋 What You'll Learn

Create a table called sales with columns salesperson (text) and total_sales (integer).

Insert the exact sales data for five salespeople: 'Alice' with 5000, 'Bob' with 7000, 'Charlie' with 3000, 'Diana' with 9000, and 'Evan' with 6000.

Write a query that selects salesperson, total_sales, and a quartile number using NTILE(4) ordered by total_sales descending.

Name the quartile column sales_quartile.

💡 Why This Matters

🌍 Real World

Companies often group employees or customers into performance or value segments to target strategies effectively.

💼 Career

Understanding NTILE helps in data analysis roles to create meaningful groups for reporting and decision-making.

1

Create the sales table and insert data

Create a table called sales with columns salesperson (text) and total_sales (integer). Then insert these exact rows: ('Alice', 5000), ('Bob', 7000), ('Charlie', 3000), ('Diana', 9000), and ('Evan', 6000).

SQL

-- Create table sales and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the NTILE function for quartiles

Write a SELECT query that retrieves salesperson and total_sales from the sales table. Add a column named sales_quartile that uses NTILE(4) to divide the salespeople into four groups ordered by total_sales descending.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    total_sales INTEGER
);

INSERT INTO sales (salesperson, total_sales) VALUES
('Alice', 5000),
('Bob', 7000),
('Charlie', 3000),
('Diana', 9000),
('Evan', 6000);

-- Write SELECT query with NTILE(4) here
-- Your code here

Hint

Use NTILE(4) OVER (ORDER BY total_sales DESC) to create quartiles.

3

Add ordering to the NTILE function

Ensure the NTILE(4) function orders the rows by total_sales in descending order inside the OVER clause. Use the exact syntax NTILE(4) OVER (ORDER BY total_sales DESC) and alias it as sales_quartile.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    total_sales INTEGER
);

INSERT INTO sales (salesperson, total_sales) VALUES
('Alice', 5000),
('Bob', 7000),
('Charlie', 3000),
('Diana', 9000),
('Evan', 6000);

SELECT salesperson, total_sales, NTILE(4) OVER (ORDER BY total_sales DESC) AS sales_quartile
FROM sales;

-- Confirm ordering in NTILE function
-- Your code here

Hint

Make sure the ORDER BY inside OVER is total_sales DESC.

4

Complete the query with correct column alias

Complete the SQL query by selecting salesperson, total_sales, and the quartile number using NTILE(4) OVER (ORDER BY total_sales DESC) aliased as sales_quartile. This final query will show each salesperson's quartile group based on their sales.

SQL

CREATE TABLE sales (
    salesperson TEXT,
    total_sales INTEGER
);

INSERT INTO sales (salesperson, total_sales) VALUES
('Alice', 5000),
('Bob', 7000),
('Charlie', 3000),
('Diana', 9000),
('Evan', 6000);

SELECT salesperson, total_sales, NTILE(4) OVER (ORDER BY total_sales DESC) AS sales_quartile
FROM sales;

-- Finalize the query with correct alias
-- Your code here

Hint

Check that the column alias is exactly sales_quartile.

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