Window functions in Snowflake - Commands & Configuration

Introduction

Sometimes you want to analyze data by looking at rows related to the current row without grouping everything together. Window functions let you do this by calculating values across a set of rows related to the current one, like running totals or rankings.

When you want to calculate a running total of sales per customer without losing individual sale details.

When you need to rank employees by salary within each department.

When you want to find the difference between each row's value and the previous row's value in a time series.

When you want to calculate averages over a sliding window of rows, like the last 7 days of data.

When you want to add row numbers to your query results for pagination or ordering.

Commands

This command calculates a running total of sales amounts for each customer ordered by the date of the order. It uses a window function SUM() OVER() to keep the individual rows while adding the running total.

Terminal

SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales ORDER BY customer_id, order_date;

Expected Output

CUSTOMER_ID | ORDER_DATE | AMOUNT | RUNNING_TOTAL

1           | 2024-01-01 | 100    | 100

1           | 2024-01-05 | 150    | 250

2           | 2024-01-02 | 200    | 200

2           | 2024-01-06 | 100    | 300

This command ranks employees by salary within each department. The RANK() window function assigns ranks without collapsing rows, so you see each employee's rank in their department.

Terminal

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

Expected Output

EMPLOYEE_ID | DEPARTMENT | SALARY | SALARY_RANK

101         | Sales      | 90000  | 1

102         | Sales      | 85000  | 2

201         | HR         | 70000  | 1

202         | HR         | 65000  | 2

This command shows each row's value and the previous row's value ordered by date. The LAG() function looks back one row to compare values.

Terminal

SELECT date, value, LAG(value, 1) OVER (ORDER BY date) AS previous_value FROM metrics ORDER BY date;

Expected Output

DATE       | VALUE | PREVIOUS_VALUE

2024-01-01 | 10    | NULL

2024-01-02 | 15    | 10

2024-01-03 | 20    | 15

Key Concept

If you remember nothing else from this pattern, remember: window functions let you calculate values across related rows without grouping and losing individual row details.

Common Mistakes

Using GROUP BY instead of window functions to calculate running totals.

GROUP BY collapses rows and loses individual row details, so you can't see running totals per row.

Use SUM() OVER() with PARTITION BY and ORDER BY to keep rows and calculate running totals.

Not specifying ORDER BY inside the OVER() clause for functions like SUM() or RANK().

Without ORDER BY, the window function does not know the order of rows, so results like running totals or ranks will be incorrect or meaningless.

Always include ORDER BY inside OVER() when order matters for the calculation.

Confusing window functions with aggregate functions without OVER(), expecting row-level results.

Aggregate functions without OVER() collapse rows into one result, losing row-level data.

Add OVER() clause to aggregate functions to turn them into window functions that keep row-level data.

Summary

Use window functions with OVER() to calculate values across related rows without grouping.

Include PARTITION BY to group rows logically and ORDER BY to define row order inside the window.

Common window functions include SUM(), RANK(), and LAG() for running totals, rankings, and comparisons.