Snowflake Nested Window Functions and Examples

Snowflake supports many useful windows or analytical functions. Many reporting queries use the analytic functions such as cumulative sum and average. But, whenever you try to call an analytics function within another analytics function, you will end up with an error such as “may not be nested inside another window function.”. In this article, we will check how to use the nested window functions in Snowflake with an alternate example.

Snowflake does not allow you to define the nested window function. You will have to use alternative methods such as common table expressions (CTEs) or create table subquery to calculate value for windows function and again apply another window function in a SELECT clause.

Snowflake Nested Window Functions

A window function is any function that operates over a window (group) of rows. A window is a group of related rows. For example, you can define a window on date, with all rows in the same month grouped in the same window.

Snowflake Lateral Column alias

Snowflake supports reusing expressions at the same level.

For example,

SELECT i         AS col_1,
       col_1 * p AS col_2,
       CASE
         WHEN col_1 > col_2 THEN 'Y'
         ELSE 'N'
       END       AS col_3
FROM   table_1;

Above query work perfectly fine on Snowflake. But, query will fail if you include multiple aggregate or window functions on column alias.

For example, following query will end up in “Aggregate functions cannot be nested” error.

SELECT Sum(i)         AS col_1,
       Avg(col_1) * p AS col_2,
       CASE
         WHEN col_1 > col_2 THEN 'Y'
         ELSE 'NO'
       END            AS col_3
FROM   table_1; 

Snowflake Nested Window Functions Using Common Table Expression (CTE)

As an alternative method, we can create a common table expression or derived tables to nest windows or analytic functions.

For example, consider following example that nest windows functions using common table expressions.

WITH tmp
     AS (SELECT SUM(i)
                  over (
                    PARTITION BY p
                    ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW)
                   cumu_sum,
                *
         FROM   table_1)
SELECT SUM(cumu_sum)
         over(
           PARTITION BY p
           ORDER BY o) AS nest_cumu_sum,
       *
FROM   tmp; 

The above query will works without any issues.

Snowflake Nested Window Functions Using Subquery

Similar to CTE, another method is to use a subquery to nest window or aggregate functions.

Consider following example that nest windows functions using common table expressions.

SELECT SUM(cumu_sum)
         over(
           PARTITION BY p
           ORDER BY o) AS nest_cumu_sum,
       *
FROM   (SELECT SUM(i)
                 over (
                   PARTITION BY p
                   ORDER BY o ROWS BETWEEN unbounded preceding AND CURRENT ROW)
                      cumu_sum,
               *
        FROM   example_cumulative) AS tmp; 

The above query will work without any issues.

Hope this helps 🙂