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

Using NTILE to Distribute Sales Data into Quartiles

📖 Scenario: You work for a retail company that wants to analyze its sales data. The company wants to divide its salespeople into four groups (quartiles) based on their total sales to understand performance distribution.

🎯 Goal: Create a PostgreSQL query that uses the NTILE window function to split salespeople into four groups 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 want to segment employees or customers into groups based on performance or behavior to target rewards or marketing.

💼 Career

Understanding window functions like NTILE is important for data analysts and database developers to perform advanced data segmentation and reporting.

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).

PostgreSQL

-- Create the sales table 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 window function

Write a SELECT query that retrieves salesperson and total_sales from the sales table. Add a column called sales_quartile that uses the NTILE(4) window function ordered by total_sales descending.

PostgreSQL

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 the 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, so the highest sales get quartile 1.

PostgreSQL

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 inside NTILE function
-- Your code here

Need a hint?

The ORDER BY clause inside OVER controls how rows are divided into quartiles.

4

Complete the query with alias and ordering

Complete the query by selecting salesperson, total_sales, and the quartile number as sales_quartile. Make sure the query orders the results by sales_quartile ascending and then by total_sales descending.

PostgreSQL

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
ORDER BY sales_quartile ASC, total_sales DESC;

-- Add ORDER BY clause to sort by quartile and sales
-- Your code here

Need a hint?

Use ORDER BY sales_quartile ASC, total_sales DESC to sort the final output.