Window functions in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding ROW_NUMBER() behavior

Given the following query on a sales table:

SELECT salesperson_id, sale_amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_amount DESC) AS rank FROM sales;

What does the ROW_NUMBER() function do in this context?

A Counts the number of sales per salesperson across all regions.

B Calculates the total sales amount per region.

C Returns the cumulative sum of sale_amount ordered by region.

D Assigns a unique rank to each sale within each region, ordered by sale_amount descending.

💡 Hint

Think about how ROW_NUMBER() assigns numbers within partitions.

ROW_NUMBER() assigns a unique sequential number to rows within each partition defined by region, ordered by sale_amount descending.

❓ service_behavior

intermediate

Effect of RANGE vs ROWS in window frame

Consider this query:

SELECT order_id, order_date, SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW) AS sum_amount FROM orders;

What is the difference in behavior if RANGE is replaced with ROWS in the window frame?

A RANGE includes all rows with order_date within 1 day before current row's date; ROWS includes exactly 1 preceding row regardless of date.

B ROWS includes all rows with order_date within 1 day before current row's date; RANGE includes exactly 1 preceding row.

C RANGE and ROWS behave identically in this query.

D RANGE causes a syntax error in Snowflake.

💡 Hint

Think about how RANGE and ROWS define the window frame differently.

RANGE uses logical offsets based on ORDER BY values (dates), so it includes all rows within 1 day before current row's date. ROWS uses physical offsets, so it includes exactly 1 preceding row regardless of date values.

❓ Configuration

advanced

Correct use of window function with NULL handling

You want to calculate the cumulative sum of sales amounts ordered by sale_date, but some sale_amount values are NULL. Which query correctly treats NULL as zero in the cumulative sum?

SELECT sale_date, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cum_sum FROM sales;

A SELECT sale_date, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_date RANGE UNBOUNDED PRECEDING) AS cum_sum FROM sales;

B SELECT sale_date, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cum_sum FROM sales WHERE sale_amount IS NOT NULL;

C SELECT sale_date, sale_amount, SUM(COALESCE(sale_amount, 0)) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cum_sum FROM sales;

D SELECT sale_date, sale_amount, SUM(NULLIF(sale_amount, 0)) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS cum_sum FROM sales;

💡 Hint

How to treat NULL values as zero in aggregation?

COALESCE replaces NULL with 0 before summing, ensuring NULLs do not cause the cumulative sum to be NULL or incorrect.

❓ security

advanced

Security implications of window functions in shared environments

In a multi-tenant Snowflake environment, what is a key security consideration when using window functions on shared tables?

A Window functions automatically encrypt data during processing.

B Window functions can expose data from other tenants if row-level security is not properly enforced.

C Window functions prevent SQL injection attacks by design.

D Window functions restrict access to only the first row of each partition.

💡 Hint

Think about data visibility and access control.

If row-level security or access controls are not correctly applied, window functions may reveal data across tenants because they operate on the full dataset before filtering.

❓ Architecture

expert

Optimizing performance of window functions on large datasets

You have a large table with millions of rows and want to optimize a query using window functions with PARTITION BY and ORDER BY clauses. Which approach will most improve query performance in Snowflake?

A Cluster the table on the columns used in PARTITION BY and ORDER BY to reduce data scanned during window function execution.

B Use CROSS JOIN to pre-aggregate data before applying window functions.

C Avoid using PARTITION BY and ORDER BY in window functions to speed up queries.

D Disable automatic clustering to let Snowflake handle data distribution.

💡 Hint

Think about how data organization affects query speed.

Clustering the table on partition and order columns helps Snowflake prune data and efficiently compute window functions on large datasets.