How to Use Window Functions in Snowflake

How do I use window functions in Snowflake SQL?

Window functions perform calculations across sets of rows related to the current row without collapsing the result set.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What are Snowflake window functions?

Window functions calculate aggregates, rankings, and running totals over a defined window of rows while preserving individual rows. They rely on the OVER() clause to specify PARTITION BY and ORDER BY scopes.

When should I use a window instead of GROUP BY?

Use GROUP BY to collapse rows into single results.Use window functions when you need row-level detail alongside aggregated insights—e.g., each order with its customer’s lifetime spend.

How do I define a window frame?

Add ROWS BETWEEN or RANGE BETWEEN in the OVER() clause.Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for cumulative calculations.

Which functions support windows?

Ranking (ROW_NUMBER, RANK, DENSE_RANK), aggregates (SUM, AVG, COUNT), distribution (PERCENT_RANK), navigation (LAG, LEAD), and analytics (NTILE) all accept OVER().

Example: Rank customers by total spend

SELECT c.id, c.name, SUM(o.total_amount) AS customer_total, RANK() OVER(ORDER BY SUM(o.total_amount) DESC) AS spend_rank FROM Orders o JOIN Customers c ON c.id = o.customer_id GROUP BY c.id, c.name;

Example: 7-day moving average of sales

SELECT order_date, SUM(total_amount) AS day_total, AVG(SUM(total_amount)) OVER(ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM Orders GROUP BY order_date ORDER BY order_date;

Best practices for window functions

1.Always PARTITION BY the smallest set that satisfies the calculation to avoid excess scanning.
2. Use ORDER BY only when ordering affects the result (e.g., running totals).
3. Persist heavy window queries into temporary tables for downstream joins.

Common use cases

• Customer lifetime value
• Order sequence numbers per customer
• Stock level deltas
• Cohort retention metrics

Why How to Use Window Functions in Snowflake is important

How to Use Window Functions in Snowflake Example Usage


-- Find each product's share of total revenue
SELECT   p.id,
         p.name,
         SUM(oi.quantity * p.price)               AS product_revenue,
         ROUND(100 * SUM(oi.quantity * p.price)
               / SUM(SUM(oi.quantity * p.price)) OVER(), 2) AS pct_of_total
FROM OrderItems oi
JOIN Products p ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY product_revenue DESC;

How to Use Window Functions in Snowflake Syntax


<pre><code>&lt;aggregate_or_analytic_function&gt; ( &lt;expression&gt; )
OVER (
    [PARTITION BY &lt;expr1&gt;, &lt;expr2&gt;, ...]
    [ORDER BY &lt;expr&gt; [ASC|DESC] [NULLS {FIRST|LAST}]]
    [ROWS|RANGE BETWEEN {UNBOUNDED PRECEDING | &lt;n&gt; PRECEDING | CURRENT ROW}
                     AND {CURRENT ROW | &lt;n&gt; FOLLOWING | UNBOUNDED FOLLOWING}]
)

-- Example: cumulative spend per customer
SUM(o.total_amount) OVER(
      PARTITION BY o.customer_id
      ORDER BY o.order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rolling_spend</code></pre>

Common Mistakes

Omitting ORDER BY in running totals. Without ORDER BY, the frame order is undefined, leading to unpredictable cumulative results. Always specify ORDER BY for time-series calculations.
Over-partitioning data. Partitioning by high-cardinality columns (e.g., order_id) limits the window to one row, nullifying the benefit. Partition only by the logical group you want to aggregate, like customer_id.