NTILE for distribution in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NTILE function do in SQL?

NTILE divides rows in a result set into a specified number of groups, assigning a group number to each row to show its distribution.

intermediate

How does NTILE assign group numbers when the rows don't divide evenly?

NTILE assigns extra rows to the first groups so that groups at the start have one more row than later groups.

beginner

Write a simple NTILE query to split 10 rows into 3 groups ordered by salary.

SELECT employee_id, salary, NTILE(3) OVER (ORDER BY salary) AS group_number FROM employees;

beginner

Why is the ORDER BY clause important in the NTILE function?

ORDER BY defines how rows are sorted before being divided into groups, affecting which rows fall into each group.

intermediate

Can NTILE be used without an ORDER BY clause? What happens?

No, NTILE requires ORDER BY to define row order; without it, the function will cause an error.

What does NTILE(4) do to a result set?

A Divides rows into 4 groups with group numbers assigned

B Sorts rows in descending order

C Counts total rows in the table

D Filters rows with values less than 4

NTILE(4) splits the rows into 4 groups and assigns each row a group number from 1 to 4.

If you have 10 rows and use NTILE(3), how many rows will the first group have?

A 2

B 3

C 4

D 5

Since 10 divided by 3 is 3 with remainder 1, the first group gets 4 rows, others get 3.

Which clause must be used with NTILE to define row order?

A GROUP BY

B ORDER BY

C WHERE

D HAVING

NTILE requires ORDER BY to sort rows before dividing them into groups.

What type of SQL function is NTILE?

A String function

B Aggregate function

C Scalar function

D Window function

NTILE is a window function that works over a set of rows.

What happens if you use NTILE(1) on a result set?

A All rows get group number 1

B Error occurs

C Each row gets a unique group number

D Rows are split into two groups

NTILE(1) puts all rows into a single group numbered 1.

Explain how NTILE distributes rows into groups and how it handles uneven division.

Think about how you would split a class into teams when the number of students isn't divisible evenly.

Describe a real-life scenario where NTILE could be useful in analyzing data.

Consider how businesses or schools categorize people based on rankings or scores.

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