Window Functions - Aggregate Functions

Question 1

Window functions in SQL differ from GROUP BY primarily because they:

A.

A) Collapse rows into groups

B.

B) Return results for every row without collapsing data

Explanation: Window functions compute values across rows while preserving all original row details, unlike GROUP BY which aggregates and reduces rows.

C.

C) Require subqueries for partitioning

D.

D) Only work with ranking functions

Question 2

What is the primary purpose of using the **OVER()** clause with the **SUM()** aggregate function?

A.

A) To group rows before aggregation

B.

B) To sort the result set by the aggregated column

C.

C) To filter rows based on aggregate values

D.

D) To compute calculations across a set of rows while retaining individual row details

**Explanation:** Window functions with **OVER()** allow aggregates like **SUM()** to operate over row sets without collapsing results into a single value.

Question 3

What does the **AVG()** function return when used as a basic aggregate without window clauses?

A.

A) A cumulative average for each row

B.

B) An average reset by partitions

C.

C) A single average value across all rows

**Explanation:** Basic **AVG()** computes one scalar average for the entire result set, unlike window versions that produce row-level values.

Question 4

What distinguishes basic **AVG(Price)** from **AVG(Price) OVER ()**?

A.

A) Basic returns row-level values

B.

B) Window version returns a single scalar

C.

C) Basic aggregates to one value; window repeats it per row

**Explanation:** Basic **AVG()** collapses to one row; **OVER ()** applies the same average to every row individually.

D.

D) They produce identical results

Question 5

What is the key difference between using COUNT() with GROUP BY versus COUNT() OVER (PARTITION BY)?

A.

A) GROUP BY returns one row per group; PARTITION BY returns all rows with aggregates repeated

Explanation: GROUP BY collapses rows into summary rows. PARTITION BY preserves individual row details and repeats the aggregate value for each row in the partition.

B.

B) PARTITION BY returns one row per group; GROUP BY returns all rows

C.

C) They are functionally identical

D.

D) PARTITION BY only works with numeric columns

Question 6

What is the primary advantage of using **MIN** as a window function instead of a traditional GROUP BY aggregate?

A.

A) Window functions execute faster than GROUP BY queries

B.

B) Window functions return all rows with partition-level aggregates, while GROUP BY reduces rows to one per group

Explanation:: Window functions compute aggregates within partitions while preserving all original rows. GROUP BY collapses multiple rows into one per group, losing row-level detail. This makes window functions ideal for comparing individual records against partition-level statistics.

C.

C) Window functions eliminate the need for JOIN operations

D.

D) Window functions automatically sort results in descending order

📝 Window Functions - Aggregate Functions

Quiz Complete!