Snowflake Window Functions

Ordinary aggregates collapse many rows into one. Window functions keep every row and add a calculated value beside it. Rankings, running totals, and comparisons with the previous row all rely on windows. Analysts use them daily because they answer questions that plain GROUP BY cannot.

The Classroom Comparison

A teacher lists every student with a score. GROUP BY would give one average for the whole class and lose the names. A window function keeps every student row and adds a rank, the class average, or the difference from the previous test, all in separate columns beside the original data.

GROUP BY VERSUS WINDOW
======================

 GROUP BY class                    WINDOW over class
 +-------+-------+                 +--------+-------+------+
 | class | avg   |                 | name   | score | rank |
 +-------+-------+                 +--------+-------+------+
 | A     | 78    |                 | Asha   | 91    | 1    |
 +-------+-------+                 | Ravi   | 78    | 2    |
 rows collapse                     | Meera  | 65    | 3    |
                                   +--------+-------+------+
                                   every row stays

The Anatomy of a Window

function() OVER (
  PARTITION BY column     -- split rows into groups (optional)
  ORDER BY column         -- sort rows inside each group
  frame                   -- which nearby rows to include (optional)
)

PARTITION BY works like GROUP BY without collapsing. ORDER BY sets the sequence for rankings and running calculations.

Ranking Functions

SELECT region, salesperson, revenue,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS row_num,
       RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_no,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rank_no
FROM sales_summary;
HOW TIES BEHAVE (revenue 500, 500, 400)
=======================================
ROW_NUMBER   1, 2, 3      always unique
RANK         1, 1, 3      ties share a rank, then a gap
DENSE_RANK   1, 1, 2      ties share a rank, no gap

Looking at Neighbor Rows

LAG reads a previous row and LEAD reads a following row. Both fit growth and change calculations.

SELECT order_month, revenue,
       LAG(revenue) OVER (ORDER BY order_month)                AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY order_month)      AS change
FROM monthly_revenue;

Running Totals and Moving Averages

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS running_total,
       AVG(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
         AS three_day_average
FROM daily_sales;
FRAME PICTURE for the three day average
=======================================
date    amount
Jan 1   100
Jan 2   120
Jan 3   140   <-- current row: average of Jan 1, 2, 3
Jan 4   160   <-- current row: average of Jan 2, 3, 4

Other Handy Functions

  • NTILE(4) splits rows into four equal buckets, such as quartiles.
  • FIRST_VALUE and LAST_VALUE return the first or last value in the window.
  • PERCENT_RANK shows relative position from 0 to 1.
  • SUM, AVG, COUNT, MIN, and MAX all work with OVER.

Filtering with QUALIFY

WHERE cannot filter on window results because windows run later. Snowflake offers QUALIFY for that job.

-- Top 3 salespeople in every region
SELECT region, salesperson, revenue
FROM sales_summary
QUALIFY RANK() OVER (PARTITION BY region ORDER BY revenue DESC) <= 3;

A Practical Scenario

A subscription company wants each customer's most recent payment and the change from the prior payment. The analyst partitions payments by customer, orders them by date, uses ROW_NUMBER with QUALIFY to keep the latest row, and uses LAG to fetch the previous amount. One query replaces a pile of spreadsheet formulas.

Common Mistakes

Forgetting ORDER BY in the Window

Running totals and rankings need a sequence. Without ORDER BY inside OVER, Snowflake treats the whole partition as one frame, and every row shows the same total.

Mixing Up RANK and ROW_NUMBER

Choose ROW_NUMBER when exactly one row per group must survive, such as the latest order. Choose RANK or DENSE_RANK when ties deserve equal treatment, such as sales leaderboards.

Expecting WHERE to Filter Window Results

WHERE runs before windows are calculated, so it cannot see a rank column. Use QUALIFY or wrap the query in a CTE and filter in the outer step.

Performance Notes

Window functions sort data inside each partition. A partition with hundreds of millions of rows needs a large warehouse and may spill to disk. Filter early, choose partition columns with moderate group sizes, and check Query Profile for sort steps that dominate the time. Several functions that share the same PARTITION BY and ORDER BY clauses reuse one sort, so group them in one SELECT.

Frequently Asked Questions

Can I use two different windows in one query?

A single SELECT may hold many window functions, each with its own OVER clause. The WINDOW clause lets you name a definition once and reuse it.

Key Points

  • Window functions add calculated columns without collapsing rows.
  • PARTITION BY splits groups; ORDER BY sets sequence.
  • ROW_NUMBER, RANK, and DENSE_RANK treat ties differently.
  • LAG and LEAD compare a row with its neighbors.
  • Frame clauses control running totals and moving averages.
  • QUALIFY filters directly on window results.
Previous lesson
Back to course
Next lesson