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.