Window functions in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a window function in Snowflake?

A window function performs a calculation across a set of table rows related to the current row, without collapsing the rows into a single output row.

beginner

What does the OVER() clause do in a Snowflake window function?

The OVER() clause defines the window or set of rows the function operates on, including partitioning and ordering rules.

intermediate

How does PARTITION BY affect a window function in Snowflake?

PARTITION BY divides the data into groups (partitions) so the window function runs separately within each group.

advanced

What is the difference between ROWS and RANGE in window framing?

ROWS counts physical rows relative to the current row; RANGE considers logical values in the order column, including ties.

beginner

Give an example of a common window function in Snowflake and its use.

ROW_NUMBER() assigns a unique number to each row within a partition, useful for ranking or deduplication.

Which clause is mandatory for a window function in Snowflake?

A OVER()

B WHERE

C GROUP BY

D HAVING

The OVER() clause is required to define the window for the function.

What does PARTITION BY do in a window function?

A Filters rows before calculation

B Groups rows for separate calculations

C Sorts rows globally

D Limits output rows

PARTITION BY groups rows so the function runs separately on each group.

Which window function assigns a unique rank to each row?

A SUM()

B AVG()

C ROW_NUMBER()

D COUNT()

ROW_NUMBER() gives a unique number to each row within its partition.

What is the default frame for window functions if not specified?

A ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

C RANGE BETWEEN CURRENT ROW AND CURRENT ROW

D RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Which keyword defines the order of rows in a window function?

A ORDER BY

B PARTITION BY

C GROUP BY

D FILTER BY

ORDER BY inside OVER() sets the order of rows for the window function.

Explain how window functions differ from aggregate functions in Snowflake.

Think about whether the number of rows changes after applying the function.

Describe how to use PARTITION BY and ORDER BY together in a window function and why.

Consider how you would rank students by class and score.