SQL Window Function Basics Quiz

1. What is the primary purpose of a window function in SQL?

To perform calculations across a set of rows related to the current row

To delete rows from a table

To create new tables automatically

To replace GROUP BY clauses entirely

A window function in SQL enables users to perform calculations across a specified range of rows, or "window," that are related to the current row. This allows for complex aggregations and analytics without collapsing the result set, preserving individual row details while still providing summary insights.

Explanation

A window function in SQL enables users to perform calculations across a specified range of rows, or "window," that are related to the current row. This allows for complex aggregations and analytics without collapsing the result set, preserving individual row details while still providing summary insights.

2. Which clause defines the subset of rows a window function operates over?

WHERE

OVER

HAVING

ORDER BY

The OVER clause specifies the window or set of rows that a window function will analyze. It allows for partitioning and ordering of data within a result set, enabling functions like ROW_NUMBER, RANK, and SUM to perform calculations across defined subsets, rather than the entire dataset.

Explanation

The OVER clause specifies the window or set of rows that a window function will analyze. It allows for partitioning and ordering of data within a result set, enabling functions like ROW_NUMBER, RANK, and SUM to perform calculations across defined subsets, rather than the entire dataset.

3. In a window function, what does PARTITION BY do?

Sorts rows in ascending order

Divides rows into logical groups for processing

Limits the number of rows returned

Filters rows before aggregation

PARTITION BY in a window function is used to divide the result set into distinct groups based on specified columns. This allows the window function to perform calculations independently within each group, enabling more granular analysis of data without collapsing it into a single result.

Explanation

PARTITION BY in a window function is used to divide the result set into distinct groups based on specified columns. This allows the window function to perform calculations independently within each group, enabling more granular analysis of data without collapsing it into a single result.

4. Which of the following is a ranking window function?

SUM()

RANK()

AVG()

COUNT()

RANK() is a ranking window function that assigns a unique rank to each row within a partition of a result set, based on the values of specified columns. Unlike aggregate functions like SUM(), AVG(), or COUNT(), which perform calculations over a set of rows, RANK() focuses on ordering individual rows.

Explanation

RANK() is a ranking window function that assigns a unique rank to each row within a partition of a result set, based on the values of specified columns. Unlike aggregate functions like SUM(), AVG(), or COUNT(), which perform calculations over a set of rows, RANK() focuses on ordering individual rows.

5. What is the difference between RANK() and DENSE_RANK()?

RANK() skips numbers after ties; DENSE_RANK() does not

DENSE_RANK() is faster than RANK()

RANK() only works with numbers; DENSE_RANK() works with any data type

There is no functional difference

RANK() assigns the same rank to tied values but skips subsequent ranks, leading to potential gaps in the ranking sequence. In contrast, DENSE_RANK() also assigns the same rank to ties but maintains a continuous ranking without gaps. This distinction affects how rankings are represented when there are ties in the data.

Explanation

RANK() assigns the same rank to tied values but skips subsequent ranks, leading to potential gaps in the ranking sequence. In contrast, DENSE_RANK() also assigns the same rank to ties but maintains a continuous ranking without gaps. This distinction affects how rankings are represented when there are ties in the data.

6. Which window function returns the value from a specified row within the window?

LAG()

LEAD()

NTH_VALUE()

All of the above

LAG(), LEAD(), and NTH_VALUE() are all window functions that allow access to specific rows within a defined window. LAG() retrieves data from a previous row, LEAD() from a subsequent row, and NTH_VALUE() returns the value from a specified row position. Thus, all three functions can return values from specified rows.

Explanation

LAG(), LEAD(), and NTH_VALUE() are all window functions that allow access to specific rows within a defined window. LAG() retrieves data from a previous row, LEAD() from a subsequent row, and NTH_VALUE() returns the value from a specified row position. Thus, all three functions can return values from specified rows.

7. What does LAG() do in a window function?

Returns the next row value

Returns the previous row value

Calculates the difference between rows

Sorts rows in descending order

LAG() is a window function that allows users to access data from a previous row within the same result set. It is commonly used to compare values across rows, enabling analysis of trends or changes over time by retrieving the value of a specified column from the preceding row.

Explanation

LAG() is a window function that allows users to access data from a previous row within the same result set. It is commonly used to compare values across rows, enabling analysis of trends or changes over time by retrieving the value of a specified column from the preceding row.

8. In the frame clause, what does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?

Include all rows from the start of the partition to the current row

Include only the current row

Include rows 1 to 100 before the current row

Include rows within 10 positions of the current row

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW specifies a window frame in SQL that includes all rows from the beginning of the partition up to and including the current row. This allows for cumulative calculations, aggregating values from the start of the specified partition to the current point in the dataset.

Explanation

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW specifies a window frame in SQL that includes all rows from the beginning of the partition up to and including the current row. This allows for cumulative calculations, aggregating values from the start of the specified partition to the current point in the dataset.

9. Which window function assigns a unique sequential number to rows?

ROW_NUMBER()

RANK()

COUNT()

NTILE()

ROW_NUMBER() is a window function that assigns a unique sequential integer to rows within a partition of a result set, starting at one for the first row. Unlike RANK() or NTILE(), which can assign the same number to ties, ROW_NUMBER() ensures that each row receives a distinct number, making it ideal for unique identification.

Explanation

ROW_NUMBER() is a window function that assigns a unique sequential integer to rows within a partition of a result set, starting at one for the first row. Unlike RANK() or NTILE(), which can assign the same number to ties, ROW_NUMBER() ensures that each row receives a distinct number, making it ideal for unique identification.

10. What does the NTILE(n) function do?

Divides rows into n equal groups and assigns bucket numbers

Calculates the nth percentile of a column

Returns the nth row in the window

Counts rows in n separate partitions

NTILE(n) is a window function in SQL that divides a result set into 'n' equal parts, or buckets. It assigns a bucket number to each row, allowing for easy analysis of data distributions across specified intervals. This is particularly useful for percentile calculations and data segmentation.

Explanation

NTILE(n) is a window function in SQL that divides a result set into 'n' equal parts, or buckets. It assigns a bucket number to each row, allowing for easy analysis of data distributions across specified intervals. This is particularly useful for percentile calculations and data segmentation.

11. Can you use aggregate functions like SUM() and AVG() as window functions?

No, they are not compatible with the OVER clause

Yes, when used with the OVER clause

Only SUM() works; AVG() does not

Yes, but only in subqueries

Aggregate functions like SUM() and AVG() can be utilized as window functions by incorporating the OVER clause, allowing calculations across a specified range of rows while retaining individual row details. This capability enhances data analysis by providing insights into subsets of data without collapsing them into single summary values.

Explanation

Aggregate functions like SUM() and AVG() can be utilized as window functions by incorporating the OVER clause, allowing calculations across a specified range of rows while retaining individual row details. This capability enhances data analysis by providing insights into subsets of data without collapsing them into single summary values.

12. What is the correct syntax order for a window function with OVER clause?

FUNCTION(column) OVER (PARTITION BY ... ORDER BY ...)

OVER (PARTITION BY ...) FUNCTION(column) ORDER BY

ORDER BY ... PARTITION BY ... FUNCTION(column)

FUNCTION OVER PARTITION ORDER (all mixed)

The syntax for a window function with the OVER clause requires the function to be specified first, followed by the OVER keyword. Inside the parentheses, the PARTITION BY clause can be used to define how to group the data, and the ORDER BY clause specifies the order of the rows within each partition. This structure ensures proper execution of the window function.

Explanation

The syntax for a window function with the OVER clause requires the function to be specified first, followed by the OVER keyword. Inside the parentheses, the PARTITION BY clause can be used to define how to group the data, and the ORDER BY clause specifies the order of the rows within each partition. This structure ensures proper execution of the window function.