Window functions in Snowflake - Mini Project: Build & Apply

Window functions in Snowflake

📖 Scenario: You work as a data analyst in a company that stores sales data in Snowflake. You want to analyze sales performance by calculating running totals and rankings within each region.

🎯 Goal: Build a Snowflake SQL query using window functions to calculate the running total of sales and rank salespeople by their sales amount within each region.

📋 What You'll Learn

Create a table called sales with columns region, salesperson, and amount.

Insert sample data into the sales table with at least 5 rows.

Write a query that uses the SUM() OVER (PARTITION BY region ORDER BY amount) window function to calculate running totals.

Write a query that uses the RANK() OVER (PARTITION BY region ORDER BY amount DESC) window function to rank salespeople within each region.

💡 Why This Matters

🌍 Real World

Window functions help analyze data trends and rankings within groups, useful in sales, finance, and reporting.

💼 Career

Understanding window functions is essential for data analysts and engineers working with cloud data warehouses like Snowflake.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns region (VARCHAR), salesperson (VARCHAR), and amount (NUMBER). Then insert these exact rows: ('East', 'Alice', 300), ('East', 'Bob', 150), ('West', 'Charlie', 200), ('West', 'Diana', 400), ('East', 'Eve', 250).

Snowflake

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE OR REPLACE TABLE to create the table and INSERT INTO to add rows.

2

Add a query to calculate running total of sales by region

Write a SELECT query on the sales table that includes columns region, salesperson, amount, and a new column running_total which uses SUM(amount) OVER (PARTITION BY region ORDER BY amount) to calculate the running total of sales within each region ordered by amount.

Snowflake

CREATE OR REPLACE TABLE sales (
  region VARCHAR,
  salesperson VARCHAR,
  amount NUMBER
);

INSERT INTO sales (region, salesperson, amount) VALUES
('East', 'Alice', 300),
('East', 'Bob', 150),
('West', 'Charlie', 200),
('West', 'Diana', 400),
('East', 'Eve', 250);

-- Write a SELECT query with running_total using SUM() OVER
-- Your code here

Hint

Use SUM(amount) OVER (PARTITION BY region ORDER BY amount) to get running totals.

3

Add ranking of salespeople by sales amount within each region

Extend the previous SELECT query to add a new column sales_rank that uses RANK() OVER (PARTITION BY region ORDER BY amount DESC) to rank salespeople by their sales amount within each region, with highest sales ranked 1.

Snowflake

CREATE OR REPLACE TABLE sales (
  region VARCHAR,
  salesperson VARCHAR,
  amount NUMBER
);

INSERT INTO sales (region, salesperson, amount) VALUES
('East', 'Alice', 300),
('East', 'Bob', 150),
('West', 'Charlie', 200),
('West', 'Diana', 400),
('East', 'Eve', 250);

SELECT
  region,
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS running_total
  -- Add sales_rank column using RANK() OVER
  -- Your code here

Hint

Use RANK() OVER (PARTITION BY region ORDER BY amount DESC) to rank salespeople.

4

Complete the query with ordering by region and sales_rank

Add an ORDER BY clause at the end of the SELECT query to order the results first by region ascending and then by sales_rank ascending.

Snowflake

CREATE OR REPLACE TABLE sales (
  region VARCHAR,
  salesperson VARCHAR,
  amount NUMBER
);

INSERT INTO sales (region, salesperson, amount) VALUES
('East', 'Alice', 300),
('East', 'Bob', 150),
('West', 'Charlie', 200),
('West', 'Diana', 400),
('East', 'Eve', 250);

SELECT
  region,
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS running_total,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank
FROM sales
-- Add ORDER BY region, sales_rank
-- Your code here

Hint

Use ORDER BY region ASC, sales_rank ASC to sort the results.

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