How to Use Window Functions in Snowflake: Syntax and Examples
In Snowflake, use
window functions by applying them with an OVER() clause to perform calculations across rows related to the current row. These functions let you compute running totals, ranks, or averages without grouping rows into a single output row.Syntax
A window function in Snowflake follows this pattern:
function_name(): The calculation to perform, likeROW_NUMBER()orSUM().OVER(): Defines the window or set of rows the function works on.- Inside
OVER(), you can specify: PARTITION BY: Divides rows into groups.ORDER BY: Sorts rows within each partition.ROWS BETWEEN: Defines the frame of rows relative to the current row.
sql
function_name() OVER ([PARTITION BY column1, column2, ...] [ORDER BY column3 [ASC|DESC], ...] [ROWS BETWEEN frame_start AND frame_end])
Example
This example shows how to assign a row number to each employee within their department, ordered by salary descending. It demonstrates ROW_NUMBER() as a window function with PARTITION BY and ORDER BY.
sql
SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department FROM employees;
Output
employee_id | department | salary | rank_in_department
------------|------------|--------|-------------------
1 | Sales | 90000 | 1
3 | Sales | 85000 | 2
2 | HR | 70000 | 1
4 | HR | 65000 | 2
Common Pitfalls
Common mistakes when using window functions in Snowflake include:
- Forgetting the
OVER()clause, which causes syntax errors. - Using
GROUP BYinstead ofPARTITION BYinsideOVER(), which are different concepts. - Not ordering rows when order matters, leading to unexpected results.
- Confusing window functions with aggregate functions that collapse rows.
sql
/* Wrong: Missing OVER() causes error */ SELECT ROW_NUMBER(); /* Right: Use OVER() with PARTITION BY and ORDER BY */ SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Quick Reference
| Window Function | Purpose | Example Usage |
|---|---|---|
| ROW_NUMBER() | Assigns unique row number per partition | ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) |
| RANK() | Ranks rows with gaps for ties | RANK() OVER (ORDER BY score DESC) |
| SUM() | Calculates running total or sum over window | SUM(sales) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| AVG() | Calculates average over window | AVG(price) OVER (PARTITION BY category) |
| LAG() | Accesses previous row's value | LAG(salary, 1) OVER (ORDER BY hire_date) |
Key Takeaways
Always use the OVER() clause to define the window for your function.
Use PARTITION BY to group rows without collapsing them like GROUP BY does.
ORDER BY inside OVER() controls the row order for functions like ROW_NUMBER or running totals.
Window functions return a value for each row, unlike aggregate functions that reduce rows.
Test your window frame with ROWS BETWEEN to control which rows are included in calculations.
