OVER() in Snowflake: Window Function Syntax & Examples

Formulas & functions · Updated November 22, 2026

In Snowflake, OVER() is the clause that turns a regular aggregate function (like SUM, COUNT, or AVG) into a window function, letting you compute values across a set of rows related to the current row without collapsing them into a single output row. You use it right after the function name, and it defines the window (the subset of rows) the function operates on. For example, SUM(sales) OVER (PARTITION BY region ORDER BY date) gives a running total per region while keeping every row visible.

What Exactly Does OVER() Do?

In standard SQL, an aggregate like SUM(amount) reduces many rows to one. When you add OVER(), you tell Snowflake to keep all rows and attach the computed value to each one. The window can be the whole table, a partition (group), or a moving frame within a partition. The syntax is:

<function> ( <expression> ) OVER ( [ PARTITION BY <expr> ] [ ORDER BY <expr> ] [ <frame> ] )

Only the parentheses after OVER are required; everything inside is optional. If you write OVER() with nothing inside, the function computes over the entire result set.

Key Components: PARTITION BY, ORDER BY, and Frame

Three pieces control the window:

  • PARTITION BY – splits rows into groups (partitions). The function resets for each partition. If omitted, the whole result set is one partition.
  • ORDER BY – defines the order of rows within each partition. Essential for running totals, rankings, and moving averages. Without it, the frame is the whole partition.
  • Frame – specifies which rows relative to the current row are included. Common frames: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running total), ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (3-row moving average), or ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (reverse running total).

If you specify ORDER BY but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same order key value as the current row. That can surprise you with ties, so many people explicitly use ROWS.

How to Choose the Right Frame

Your frame determines the meaning of the result. Here are common scenarios:

  • Running total: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) – cumulative sum up to each row.
  • Moving average: AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) – 7-day average.
  • Whole partition aggregate: SUM(amount) OVER (PARTITION BY region) – total per region, repeated on each row.
  • Percent of total: amount / SUM(amount) OVER (PARTITION BY region) * 100 – share of each row within its region.

When you need a “previous row” value, use LAG or LEAD instead of a frame. For rankings, RANK, DENSE_RANK, and ROW_NUMBER automatically use the whole partition with ORDER BY – they ignore frame clauses.

Common Problems and Misconceptions

One frequent mistake is forgetting that OVER (ORDER BY ...) without a frame can include ties. For example, if two rows have the same date, a running total might include both rows’ values at the first occurrence, which may not be what you expect. Fix it by adding ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Another issue: using OVER() with a non-deterministic order. If you don’t specify ORDER BY and don’t partition, Snowflake may compute the function over an arbitrary order, giving inconsistent results. Always specify a deterministic order if the result depends on row order.

Some users think OVER() can be used in a WHERE clause – it cannot. Window functions are evaluated after WHERE, GROUP BY, and HAVING, so you must filter first, then compute the window. To filter on a window result, use a subquery or a QUALIFY clause (Snowflake-specific).

OVER() vs. GROUP BY

The main difference: GROUP BY collapses rows into one per group, losing detail. OVER() keeps all rows and adds a computed column. For example, to get each employee’s salary and the department average:

  • With GROUP BY you’d get only department averages, not individual rows.
  • With OVER (PARTITION BY dept) you get every employee row plus the department average.

If you need both, you can combine them: first aggregate with GROUP BY, then use a window function on the aggregated result. But in most reporting scenarios, OVER() is the cleaner choice because it avoids extra joins.

Performance and Cost Considerations

Window functions are generally efficient in Snowflake, but they can be memory-intensive if the partition is huge or the frame is large. Snowflake’s virtual warehouse handles them, but you can reduce cost by:

  • Filtering rows early (in WHERE) to shrink the dataset.
  • Using PARTITION BY on a column with low cardinality to split work.
  • Avoiding extremely large frames (e.g., ROWS BETWEEN 100000 PRECEDING AND CURRENT ROW) unless necessary.

There’s no extra charge for using OVER() itself – you pay for the compute time of the warehouse. So optimizing queries with window functions can lower your credit consumption.

Alternatives to OVER()

Sometimes you don’t need a window function. If you only need a single aggregate per group, use GROUP BY. To compare a row to a previous one, LAG and LEAD are window functions too, but they are simpler than writing a frame. For filtering on a window result, Snowflake’s QUALIFY clause lets you use a window function in a filter without a subquery. For example, to get the top 3 sales per region:

SELECT * FROM sales QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) <= 3;

If you need a moving average, you could also use AVG with a frame, but a self-join is an alternative – though usually slower and more verbose.

Frequently Asked Questions

Can I use OVER() without PARTITION BY?

Yes. OVER() with no partition treats the entire result set as one group. For example, SUM(sales) OVER () gives the grand total on every row. This is handy for calculating percentages of a total.

What’s the difference between ROWS and RANGE in the frame?

ROWS counts physical rows, while RANGE includes rows whose order key values fall within a range relative to the current row’s value. For example, RANGE BETWEEN 5 PRECEDING AND CURRENT ROW includes all rows with order key values within 5 of the current value. ROWS is more common and predictable.

Does OVER() work with COUNT DISTINCT?

Yes, Snowflake supports COUNT(DISTINCT col) OVER (PARTITION BY ...). It counts distinct values within each partition and attaches that number to each row. Be aware it can be slower than a regular COUNT(DISTINCT) because it must track distinct values per partition.

Can I use OVER() in a WHERE clause?

No. Window functions are evaluated after WHERE, so you cannot reference them there. Use a subquery or the QUALIFY clause to filter on a window result. For example, SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn FROM t) WHERE rn = 1.

Is OVER() the same as a correlated subquery?

No. A correlated subquery computes a value for each row by running a separate query, which can be slow. A window function computes once over the window and is generally faster and more readable. Use OVER() when you need an aggregate that doesn’t collapse rows.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.