Window functions in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales using a window function.

Snowflake

SELECT sales, SUM(sales) OVER (ORDER BY [1]) AS running_total FROM sales_data;

Drag options to blanks, or click blank then click option'

Acustomer_id

Bsales

Cdate

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by sales value instead of date.

Using a non-time related column for ordering.

The running total should be ordered by the date to accumulate sales over time.

2fill in blank

medium

Complete the code to assign a rank to each employee based on their salary within their department.

Snowflake

SELECT employee_id, department, salary, RANK() OVER (PARTITION BY [1] ORDER BY salary DESC) AS salary_rank FROM employees;

Drag options to blanks, or click blank then click option'

Aregion

Bemployee_id

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by salary or employee_id instead of department.

Not partitioning at all, which ranks across all employees.

Partitioning by department groups employees so ranks are assigned within each department.

3fill in blank

hard

Fix the error in the code to calculate the moving average of sales over the last 3 days.

Snowflake

SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN [1] PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

A2

B1

C5

D7

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 preceding row which calculates a 2-day average.

Using 5 or 7 preceding rows which includes too many days.

The window frame should include the current row and the 2 preceding rows to calculate a 3-day moving average.

4fill in blank

hard

Fill both blanks to calculate the difference between each sale and the previous sale within each region.

Snowflake

SELECT region, sales, sales - LAG([1]) OVER (PARTITION BY [2] ORDER BY date) AS sales_diff FROM sales_data;

Drag options to blanks, or click blank then click option'

Asales

Bdate

Cregion

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using date or customer_id in LAG instead of sales.

Partitioning by date or customer_id instead of region.

The LAG function should look at the sales column, and partitioning should be done by region to compare sales within the same region.

5fill in blank

hard

Fill all three blanks to calculate the cumulative sum of sales per product category ordered by date.

Snowflake

SELECT category, date, sales, SUM([1]) OVER (PARTITION BY [2] ORDER BY [3] ROWS UNBOUNDED PRECEDING) AS cumulative_sales FROM sales_data;

Drag options to blanks, or click blank then click option'

Asales

Bcategory

Cdate

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Summing category or date instead of sales.

Partitioning by region instead of category.

Ordering by sales instead of date.

The SUM function should sum the sales column, partitioned by category, and ordered by date to get cumulative sales per category over time.