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.

Practice

1. What does a window function in Snowflake do?

easy

A. Calculates values across rows related to the current row without grouping them into fewer rows

B. Groups rows and reduces the number of rows returned

C. Deletes duplicate rows from the result set

D. Creates a new table from existing data

Solution

1. Step 1: Understand window function purpose

   Window functions perform calculations across a set of rows related to the current row but do not reduce the number of rows returned.

2. Step 2: Compare with grouping

   Unlike GROUP BY, window functions keep all rows visible while calculating values like running totals or ranks.

3. Final Answer:

   Calculates values across rows related to the current row without grouping them into fewer rows -> Option A

4. Quick Check:

   Window functions analyze rows without grouping = A [OK]

Hint: Window functions keep all rows, unlike GROUP BY [OK]

Common Mistakes:

• Confusing window functions with GROUP BY aggregation
• Thinking window functions reduce row count
• Assuming window functions delete duplicates

2. Which of the following is the correct syntax to calculate a running total of sales using a window function in Snowflake?

easy

A. SELECT SUM(sales) GROUP BY region ORDER BY date FROM sales_data;

B. SELECT sales + PREVIOUS(sales) FROM sales_data;

C. SELECT RUNNING_TOTAL(sales) FROM sales_data;

D. SELECT SUM(sales) OVER (PARTITION BY region ORDER BY date) FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   SUM(sales) OVER (PARTITION BY region ORDER BY date) correctly calculates a running total partitioned by region and ordered by date.

2. Step 2: Eliminate incorrect options

   SELECT SUM(sales) GROUP BY region ORDER BY date FROM sales_data; uses GROUP BY which reduces rows, not a window function. Options C and D use invalid functions or syntax.

3. Final Answer:

   SELECT SUM(sales) OVER (PARTITION BY region ORDER BY date) FROM sales_data; -> Option D

4. Quick Check:

   SUM() OVER with PARTITION BY and ORDER BY = B [OK]

Hint: Look for SUM() OVER with PARTITION BY and ORDER BY [OK]

Common Mistakes:

• Using GROUP BY instead of OVER clause
• Using non-existent functions like RUNNING_TOTAL
• Omitting ORDER BY in window function

3. Given the table sales with columns region, date, and amount, what is the output of this query?

SELECT region, date, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank FROM sales;

medium

A. Ranks sales amounts within each region from highest to lowest

B. Ranks sales amounts across all regions ignoring region groups

C. Calculates cumulative sum of amounts per region

D. Returns the total number of sales per region

Solution

1. Step 1: Understand RANK() with PARTITION BY and ORDER BY

   RANK() assigns ranks starting at 1 within each partition (region), ordering by amount descending.

2. Step 2: Interpret the query output

   The query shows each sale with its rank in its region based on amount, highest amount ranked 1.

3. Final Answer:

   Ranks sales amounts within each region from highest to lowest -> Option A

4. Quick Check:

   RANK() OVER PARTITION BY region ORDER BY amount DESC = A [OK]

Hint: RANK() with PARTITION BY ranks within groups [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with cumulative sum
• Assuming ranks are across all rows without grouping

4. Identify the error in this Snowflake query:

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary) PARTITION BY department FROM employees;

medium

A. ORDER BY cannot be used in window functions

B. ROW_NUMBER() cannot be used with ORDER BY

C. PARTITION BY must come before ORDER BY inside OVER()

D. Missing GROUP BY clause for department

Solution

1. Step 1: Check window function clause order

   In Snowflake, PARTITION BY must appear before ORDER BY inside the OVER() clause.

2. Step 2: Identify syntax error

   The query places PARTITION BY after ORDER BY, which is invalid syntax.

3. Final Answer:

   PARTITION BY must come before ORDER BY inside OVER() -> Option C

4. Quick Check:

   PARTITION BY before ORDER BY in OVER() = D [OK]

Hint: PARTITION BY always before ORDER BY in OVER() [OK]

Common Mistakes:

• Placing PARTITION BY after ORDER BY
• Thinking ROW_NUMBER() disallows ORDER BY
• Adding unnecessary GROUP BY for window functions

5. You want to calculate the average sales per region and also show each sale's rank by amount within its region. Which query correctly combines these using window functions?

hard

A. SELECT region, amount, AVG(amount) PARTITION BY region, RANK() ORDER BY amount DESC FROM sales;

B. SELECT region, amount, AVG(amount) OVER (PARTITION BY region) AS avg_region, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank FROM sales;

C. SELECT region, amount, AVG(amount), RANK() FROM sales GROUP BY region ORDER BY amount DESC;

D. SELECT region, amount, AVG(amount) OVER (), RANK() OVER (ORDER BY amount) FROM sales;

Solution

1. Step 1: Use AVG() as window function partitioned by region

   AVG(amount) OVER (PARTITION BY region) calculates average sales per region without grouping rows.

2. Step 2: Use RANK() partitioned by region ordered by amount descending

   RANK() OVER (PARTITION BY region ORDER BY amount DESC) ranks sales within each region.

3. Step 3: Verify query correctness

   SELECT region, amount, AVG(amount) OVER (PARTITION BY region) AS avg_region, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank FROM sales; correctly uses window functions with proper syntax and clauses.

4. Final Answer:

   SELECT region, amount, AVG(amount) OVER (PARTITION BY region) AS avg_region, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank FROM sales; -> Option B

5. Quick Check:

   AVG() and RANK() with PARTITION BY region = C [OK]

Hint: Use OVER(PARTITION BY region) for both AVG and RANK [OK]

Common Mistakes:

• Using GROUP BY instead of window functions
• Incorrect syntax for window functions
• Omitting PARTITION BY for per-region calculations