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

Need a 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

Need a 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

Need a 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

Need a hint?

Check that the column alias is exactly sales_quartile.