Window Functions

1. Window Functions

In this video you'll learn how window functions let you compute calculations across related rows without collapsing your result set the way GROUP BY does.

2. What is a Window Function?

A window function computes a result for each row by looking at a surrounding set of rows - its window. Unlike GROUP BY, it doesn't collapse rows. Every row goes in, every row comes out, with the window calculation added as a new column. The window is defined entirely by the OVER clause - that's where you specify which rows belong to each calculation and in what order. There are three main categories: ranking, aggregation and navigation. If this sounds overwhelming and you're completely new to Window functions, we recommend taking the Window Functions in Snowflake course.

3. The OVER() Clause

Inside the OVER part of a window function, two clauses do the work. PARTITION BY divides rows into groups - here, one window per region. Each partition is independent, meaning the calculation restarts for APAC separately from EMEA. ORDER BY within OVER determines the sequence rows are processed in. It's not sorting the output - it's defining which rows come before which inside the window. Notice the average for EMEA updates as each row arrives: 3, then 4, then 4. The average is recalculated cumulatively as the window grows row by row.

4. ROW_NUMBER vs RANK vs DENSE_RANK

Three of the most common window functions are ranking functions: ROW_NUMBER, RANK, and DENSE_RANK. They all assign a position within a partition but handle ties differently. Looking at the output, SHP-002 and SHP-003 both have 5 delivery days — that's the tie that shows the difference. ROW_NUMBER always produces a unique number. The two tied rows get 2 and 3 — which one gets which is arbitrary. RANK shares the rank on a tie, but skips the next number. Both rows get 2, and the next row jumps straight to 4. DENSE_RANK also shares the rank on a tie, but doesn't skip. Both rows get 2, and the next row is 3. Choose based on what you need downstream — leaderboards usually want DENSE_RANK, while pagination logic typically needs ROW_NUMBER.

5. LAG and LEAD

Let’s look at how we can use window functions to look back in time by reviewing LAG. LAG looks backward, so LAG(credits_used, 1) reaches one row behind the current row within the same partition and gives you the previous month's value without a self-join. The second argument is the offset, meaning how many rows do you want to look back. The first row in each partition has no predecessor, so it returns NULL. LEAD is the mirror image: it looks forward like we have in our example for next_month_credits.

6. Running Totals and Rolling Averages

Window frames control exactly which rows are included in each calculation. The frame clause is always the same structure — ROWS BETWEEN a start point and an end point — but changing the start point completely changes the behaviour. On the left, UNBOUNDED PRECEDING anchors the window at the very first row. As we move down, the window keeps growing — Jan, then Jan to Feb, then Jan to March. That's a running total. On the right, 2 PRECEDING means only this row plus the two before it. The window size stays fixed at three months, but it slides forward with each row — so by April, January has dropped out. That's a rolling average. Same syntax, two completely different behaviours — just by changing the start point.

7. ROWS vs RANGE frames

The frame clause has two modes — ROWS and RANGE — and they behave very differently. ROWS counts physical positions. ROWS BETWEEN 2 PRECEDING always includes exactly this row plus the two before it, regardless of their values. Predictable and precise. RANGE works on values. RANGE BETWEEN 2 PRECEDING includes every row whose ORDER BY value falls within 2 units of the current row. If multiple rows share the same value, they're all pulled in together. For running totals and rolling averages, ROWS is almost always what you want — the result is exact and deterministic. Reach for RANGE only when your logic is about value boundaries, not row position.

8. Let's practice!

You've covered the key window functions in Snowflake - OVER, PARTITION BY, ranking functions, LAG and LEAD, and window frames including the difference between ROWS and RANGE. For a deeper dive, check out the Window Functions in Snowflake course here on DataCamp. Now let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.