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

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

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

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

Hint

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

Practice

1. What does the NTILE(n) function do in PostgreSQL?

easy

A. It returns the maximum value of a column.

B. It counts the total number of rows in a table.

C. It divides rows into n groups with roughly equal size, numbering each group.

D. It sorts rows alphabetically by a specified column.

Solution

1. Step 1: Understand NTILE purpose

   NTILE(n) splits rows into n groups based on order.

2. Step 2: Recognize group numbering

   Each group is numbered starting from 1 to n, distributing rows evenly.

3. Final Answer:

   It divides rows into n groups with roughly equal size, numbering each group. -> Option C

4. Quick Check:

   NTILE splits rows into groups [OK]

Hint: NTILE splits rows into equal groups numbered 1 to n [OK]

Common Mistakes:

• Confusing NTILE with COUNT or MAX functions
• Thinking NTILE sorts rows alphabetically
• Assuming NTILE returns row counts

2. Which of the following is the correct syntax to assign quartiles using NTILE in PostgreSQL?

easy

A. SELECT column, NTILE(4) PARTITION BY column FROM table;

B. SELECT column, NTILE OVER (4 ORDER BY column) AS quartile FROM table;

C. SELECT column, NTILE(4) AS quartile FROM table ORDER BY column;

D. SELECT column, NTILE(4) OVER (ORDER BY column) AS quartile FROM table;

Solution

1. Step 1: Recall NTILE syntax

   The correct syntax is NTILE(n) OVER (ORDER BY column) to assign groups.

2. Step 2: Check each option

   SELECT column, NTILE(4) OVER (ORDER BY column) AS quartile FROM table; uses correct syntax with NTILE(4) OVER (ORDER BY column). Others misuse OVER clause or omit it.

3. Final Answer:

   SELECT column, NTILE(4) OVER (ORDER BY column) AS quartile FROM table; -> Option D

4. Quick Check:

   NTILE needs OVER with ORDER BY [OK]

Hint: NTILE requires OVER and ORDER BY clauses [OK]

Common Mistakes:

• Omitting OVER clause
• Placing number inside OVER incorrectly
• Using PARTITION BY without ORDER BY

3. Given the table scores with column score values: 10, 20, 30, 40, 50, what is the output of this query?

SELECT score, NTILE(3) OVER (ORDER BY score) AS tile FROM scores ORDER BY score;

medium

A. [{10,1}, {20,1}, {30,2}, {40,2}, {50,3}]

B. [{10,1}, {20,2}, {30,2}, {40,3}, {50,3}]

C. [{10,1}, {20,1}, {30,1}, {40,2}, {50,3}]

D. [{10,1}, {20,2}, {30,3}, {40,3}, {50,3}]

Solution

1. Step 1: Understand NTILE(3) distribution

   NTILE(3) divides 5 rows into 3 groups as evenly as possible: 2 rows in first group, 2 in second, 1 in third.

2. Step 2: Assign tiles by order

   Ordered scores: 10,20 (tile 1), 30,40 (tile 2), 50 (tile 3).

3. Final Answer:

   [{10,1}, {20,1}, {30,2}, {40,2}, {50,3}] -> Option A

4. Quick Check:

   5 rows split into 3 groups: 2,2,1 [OK]

Hint: Divide rows evenly; extra rows go to earlier tiles [OK]

Common Mistakes:

• Assigning one row per tile ignoring total rows
• Not ordering rows before applying NTILE
• Assuming equal rows per tile always

4. Identify the error in this query:

SELECT id, NTILE(4) ORDER BY score FROM results;

medium

A. Missing OVER() clause after NTILE(4).

B. ORDER BY cannot be used inside NTILE function.

C. NTILE requires a PARTITION BY clause.

D. The query is correct as is.

Solution

1. Step 1: Check NTILE syntax

   NTILE requires an OVER() clause with ORDER BY inside it, like NTILE(4) OVER (ORDER BY score).

2. Step 2: Identify missing OVER()

   The query misses the OVER() clause, causing syntax error.

3. Final Answer:

   Missing OVER() clause after NTILE(4). -> Option A

4. Quick Check:

   NTILE needs OVER() with ORDER BY [OK]

Hint: NTILE always needs OVER() with ORDER BY [OK]

Common Mistakes:

• Placing ORDER BY outside OVER()
• Forgetting OVER() clause
• Assuming PARTITION BY is mandatory

5. You want to divide 7 employees into 3 salary groups using NTILE, but the salaries are:
5000, 7000, 7000, 9000, 11000, 11000, 13000.
Which NTILE(3) group assignment is correct when ordered by salary?

hard

A. [{5000,1}, {7000,1}, {7000,1}, {9000,2}, {11000,2}, {11000,3}, {13000,3}]

B. [{5000,1}, {7000,1}, {7000,2}, {9000,3}, {11000,3}, {11000,3}, {13000,3}]

C. [{5000,1}, {7000,2}, {7000,2}, {9000,2}, {11000,3}, {11000,3}, {13000,3}]

D. [{5000,1}, {7000,1}, {7000,2}, {9000,2}, {11000,3}, {11000,3}, {13000,3}]

Solution

1. Step 1: Calculate group sizes for 7 rows and 3 groups

   7 rows divided into 3 groups: first group gets 3 rows, next two get 2 rows each.

2. Step 2: Assign groups by ordered salary

   Order: 5000(1), 7000(1), 7000(2), 9000(3), 11000(3), 11000(3), 13000(3).

3. Final Answer:

   [{5000,1}, {7000,1}, {7000,2}, {9000,3}, {11000,3}, {11000,3}, {13000,3}] -> Option B

4. Quick Check:

   7 rows split into 3 groups: 3,2,2 [OK]

Hint: Extra rows go to earlier groups; order matters [OK]

Common Mistakes:

• Ignoring duplicate salaries affecting group boundaries
• Assigning equal rows per group ignoring total rows
• Misplacing group numbers for duplicates