Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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, like ROW_NUMBER() or SUM().
  • 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 BY instead of PARTITION BY inside OVER(), 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 FunctionPurposeExample Usage
ROW_NUMBER()Assigns unique row number per partitionROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK()Ranks rows with gaps for tiesRANK() OVER (ORDER BY score DESC)
SUM()Calculates running total or sum over windowSUM(sales) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG()Calculates average over windowAVG(price) OVER (PARTITION BY category)
LAG()Accesses previous row's valueLAG(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.