NTILE for distribution in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding NTILE distribution buckets

Given the table sales with columns id and amount, what is the output of this query?

SELECT id, amount, NTILE(3) OVER (ORDER BY amount) AS bucket FROM sales ORDER BY id;

PostgreSQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales VALUES (1, 100), (2, 200), (3, 300), (4, 400), (5, 500), (6, 600), (7, 700);

SELECT id, amount, NTILE(3) OVER (ORDER BY amount) AS bucket FROM sales ORDER BY id;

A [{"id":1,"amount":100,"bucket":1},{"id":2,"amount":200,"bucket":1},{"id":3,"amount":300,"bucket":2},{"id":4,"amount":400,"bucket":2},{"id":5,"amount":500,"bucket":3},{"id":6,"amount":600,"bucket":3},{"id":7,"amount":700,"bucket":3}]

B [{"id":1,"amount":100,"bucket":1},{"id":2,"amount":200,"bucket":2},{"id":3,"amount":300,"bucket":2},{"id":4,"amount":400,"bucket":3},{"id":5,"amount":500,"bucket":3},{"id":6,"amount":600,"bucket":3},{"id":7,"amount":700,"bucket":3}]

C [{"id":1,"amount":100,"bucket":1},{"id":2,"amount":200,"bucket":1},{"id":3,"amount":300,"bucket":2},{"id":4,"amount":400,"bucket":3},{"id":5,"amount":500,"bucket":3},{"id":6,"amount":600,"bucket":3},{"id":7,"amount":700,"bucket":3}]

D [{"id":1,"amount":100,"bucket":1},{"id":2,"amount":200,"bucket":1},{"id":3,"amount":300,"bucket":1},{"id":4,"amount":400,"bucket":2},{"id":5,"amount":500,"bucket":2},{"id":6,"amount":600,"bucket":3},{"id":7,"amount":700,"bucket":3}]

💡 Hint

NTILE divides rows into equal groups as evenly as possible, ordered by the specified column.

NTILE(3) splits 7 rows into 3 buckets. The first bucket gets 3 rows, the next two buckets get 2 rows each. Rows are ordered by amount ascending.

❓ query_result

intermediate

NTILE with ties in ordering column

Consider the table scores with columns player and score. What is the output of this query?

SELECT player, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM scores ORDER BY player;

PostgreSQL

CREATE TABLE scores (player TEXT, score INT);
INSERT INTO scores VALUES ('Alice', 90), ('Bob', 80), ('Charlie', 80), ('David', 70), ('Eve', 60), ('Frank', 50), ('Grace', 40), ('Heidi', 30);

SELECT player, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM scores ORDER BY player;

A [{"player":"Alice","score":90,"quartile":1},{"player":"Bob","score":80,"quartile":1},{"player":"Charlie","score":80,"quartile":2},{"player":"David","score":70,"quartile":2},{"player":"Eve","score":60,"quartile":3},{"player":"Frank","score":50,"quartile":3},{"player":"Grace","score":40,"quartile":4},{"player":"Heidi","score":30,"quartile":4}]

B [{"player":"Alice","score":90,"quartile":1},{"player":"Bob","score":80,"quartile":1},{"player":"Charlie","score":80,"quartile":1},{"player":"David","score":70,"quartile":2},{"player":"Eve","score":60,"quartile":2},{"player":"Frank","score":50,"quartile":3},{"player":"Grace","score":40,"quartile":4},{"player":"Heidi","score":30,"quartile":4}]

C [{"player":"Alice","score":90,"quartile":1},{"player":"Bob","score":80,"quartile":2},{"player":"Charlie","score":80,"quartile":2},{"player":"David","score":70,"quartile":2},{"player":"Eve","score":60,"quartile":3},{"player":"Frank","score":50,"quartile":3},{"player":"Grace","score":40,"quartile":4},{"player":"Heidi","score":30,"quartile":4}]

D [{"player":"Alice","score":90,"quartile":1},{"player":"Bob","score":80,"quartile":2},{"player":"Charlie","score":80,"quartile":2},{"player":"David","score":70,"quartile":3},{"player":"Eve","score":60,"quartile":3},{"player":"Frank","score":50,"quartile":4},{"player":"Grace","score":40,"quartile":4},{"player":"Heidi","score":30,"quartile":4}]

💡 Hint

NTILE assigns rows evenly but does not group ties together; ordering is strict.

NTILE(4) divides 8 rows into 4 buckets of 2 rows each ordered by score descending. So first 2 rows bucket 1, next 2 bucket 2, etc. Bob and Charlie both have 80 but are split across buckets 1 and 2.

📝 Syntax

advanced

Identify the syntax error in NTILE usage

Which option contains a syntax error when using NTILE in PostgreSQL?

A SELECT id, NTILE(4) OVER (ORDER BY amount) AS quartile FROM sales;

B SELECT id, NTILE() OVER (ORDER BY amount) AS quartile FROM sales;

C SELECT id, NTILE(4) OVER (PARTITION BY category ORDER BY amount) AS quartile FROM sales;

D SELECT id, NTILE(4) OVER (ORDER BY amount DESC) AS quartile FROM sales;

💡 Hint

NTILE requires a number inside the parentheses.

NTILE() without a number inside parentheses is a syntax error. It must be NTILE(n) where n is a positive integer.

❓ optimization

advanced

Optimizing NTILE calculation on large data

You want to assign NTILE(10) buckets on a large table transactions ordered by transaction_date. Which approach is most efficient?

A Create an index on transaction_date and then use NTILE(10) OVER (ORDER BY transaction_date).

B Use a subquery to first order by transaction_date, then apply NTILE(10) in outer query.

C Use NTILE(10) OVER (ORDER BY transaction_date) directly in the SELECT without indexes.

D Calculate row_number() OVER (ORDER BY transaction_date) and then divide manually into buckets.

💡 Hint

Indexes help speed up ORDER BY operations used in window functions.

Creating an index on the ordering column helps PostgreSQL efficiently order rows for NTILE calculation, improving performance on large tables.

🧠 Conceptual

expert

NTILE behavior with uneven row counts

If a table has 11 rows and you use NTILE(4), how many rows will be in each bucket?

A Buckets 1 and 2 have 2 rows each; buckets 3 and 4 have 3 rows each.

B Buckets 1 and 2 have 3 rows each; buckets 3 and 4 have 2 rows each.

C Buckets 1, 2, and 3 have 3 rows each; bucket 4 has 2 rows.

D Buckets 1, 2, 3, and 4 all have 3 rows each.

💡 Hint

NTILE tries to distribute rows as evenly as possible, with earlier buckets getting the extra rows.

11 rows divided into 4 buckets means 2 or 3 rows per bucket. The first buckets get the extra rows, so buckets 1, 2, and 3 have 3 rows, bucket 4 has 2 rows.

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