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

Need a 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

Need a 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

Need a 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

Need a hint?

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