Window Functions in Snowflake
Jake Roach
Field Data Engineer
SELECT ... AVG(<1>) OVER( PARTITION BY ... ORDER BY <2> -- Window between the first -- and current row
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) ...
ROWS BETWEEN
allows us to create a dynamic window frame
UNBOUNDED PRECEEDING AND CURRENT ROW
CURRENT ROW AND UNBOUNDED FOLLOWING
$$
<1>
: field to take calculation of
<2>
: sequences results, builds window frame
SELECT member_id AS m_id, calories_burned AS cb, -- Running total! SUM(calories_burned) OVER( PARTITION BY member_id ORDER BY workout_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total FROM fitness.workouts;
m_id | cb | running_total
------- | ------- | --------------
m_192 | 105 | 105
m_192 | 156 | 261
m_192 | 69 | 330
m_192 | 102 | 432
m_74 | 374 | 374
m_74 | 396 | 770
m_74 | 504 | 1274
m_233 | 51 | 51
m_233 | 81 | 132
SELECT member_id AS m_id, calories_burned AS cb, SUM(calories_burned) OVER( PARTITION BY member_id ORDER BY workout_date -- Window between current row and last
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS left_to_burn FROM fitness.workouts;
m_id | cb | left_to_burn
------- | ------- | --------------
m_192 | 105 | 432
m_192 | 156 | 327
m_192 | 69 | 171
m_192 | 102 | 102
m_74 | 374 | 1274
m_74 | 396 | 900
m_74 | 504 | 504
m_233 | 51 | 132
m_233 | 81 | 81
SELECT
member_id,
workout_date,
calories_burned,
AVG(calories_burned) OVER( -- Running average of calories burned
PARTITION BY member_id
-- Crate a window by workout date, from the first workout to the current workout
ORDER BY workout_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS avg_calories_burned
FROM FITNESS.workouts;
member_id | workout_date | calories_burned | average_calories_burned
----------- | -------------- | ----------------- | -------------------------
m_192 | 2025-01-01 | 105 | 105.0
m_192 | 2025-01-03 | 156 | 130.5
m_192 | 2025-01-04 | 69 | 110.0
m_192 | 2025-01-10 | 102 | 108.0
m_74 | 2025-02-10 | 374 | 374.0
m_74 | 2025-02-13 | 396 | 385.0
m_74 | 2025-02-14 | 504 | 426.7
m_233 | 2025-03-05 | 51 | 51.0
m_233 | 2025-03-12 | 81 | 66.0
Window Functions in Snowflake