Window Functions - Ranking Functions

Question 1

Which of the following best describes the primary difference between **window functions** and **aggregate functions**?

A.

A) Window functions return a result row for each input row, while aggregate functions collapse groups into single values.

Explanation: Window functions like ROW_NUMBER() compute a value for each row while preserving the original row in the output. Aggregate functions (AVG, COUNT, MAX, MIN) collapse grouped data into summary values, reducing row count.

B.

B) Window functions return a single value per group, while aggregate functions return multiple rows.

C.

C) Window functions can only be used with PARTITION BY, while aggregate functions cannot.

D.

D) Window functions are faster than aggregate functions in all scenarios.

Question 2

Which clause in a window function specifies the grouping for aggregate calculations?

A.

A) GROUP BY

B.

B) PARTITION BY

Explanation: PARTITION BY divides rows into groups for window function calculations. GROUP BY collapses rows; PARTITION BY keeps all rows visible with aggregates repeated per partition.

C.

C) ORDER BY

D.

D) HAVING

Question 3

What is the primary difference between **RANK()** and **DENSE_RANK()** window functions?

A.

A) **RANK()** skips ranks after ties, while **DENSE_RANK()** does not.

Explanation:**RANK()** assigns the same rank to ties but skips subsequent ranks (e.g., 1,2,2,4), whereas **DENSE_RANK()** continues sequentially (e.g., 1,2,2,3).

B.

B) **RANK()** requires **PARTITION BY**, while **DENSE_RANK()** does not.

C.

C) **DENSE_RANK()** sorts in descending order by default, while **RANK()** sorts ascending.

D.

D) **RANK()** works only on numeric columns, while **DENSE_RANK()** works on any column.

Question 4

Which scenario best suits **DENSE_RANK()** over **RANK()**?

A.

A) When gaps in numbering after ties are acceptable.

B.

B) When sequential numbering without gaps after ties is needed.

**Explanation:** **DENSE_RANK()** ensures no rank gaps after ties, providing continuous numbering.

C.

C) When partitioning by multiple columns.

D.

D) When descending order is required.

Question 5

What does the **NTILE()** window function primarily accomplish?

A.

A) Assigns unique sequential numbers to rows

B.

B) Divides rows into a specified number of equally-sized groups

Explanation: NTILE() distributes rows across N buckets for categorization, unlike ranking functions that provide sequential or gapped ranks.

C.

C) Ranks rows with gaps for tied values

D.

D) Counts the number of rows in each partition

Question 6

How does **NTILE(n)** differ from **RANK()** and **DENSE_RANK()**?

A.

A) It caps rankings at the fixed value n

Explanation: NTILE() limits ranks to 1 through n, enabling fixed segmentation rather than unlimited ranks from other functions.

B.

B) It allows unlimited ranking levels based on ties

C.

C) It ignores the ORDER BY clause

D.

D) It requires a PARTITION BY clause always

📝 Window Functions - Ranking Functions

Quiz Complete!