Window Functions in Snowflake
Jake Roach
Field Data Engineer
SELECT ... AVG(<1>) OVER( PARTITION BY ... ORDER BY <2> -- Window between the X preceding -- and Y following records
ROWS BETWEEN <X> PRECEDING AND <Y> FOLLOWING
) ...
Specify the number of records before and after the current row
ROWS BETWEEN X PRECEDING AND Y FOLLOWING
CURRENT ROW
$$
<X>
: # of rows to look back
<Y>
: # of rows to look ahead
SELECT member_id, workout_date, calories_burned, -- Create a moving average using a window using the previous workout, -- current workout, and next workout AVG(calories_burned) OVER( PARTITION BY member_id ORDER BY workout_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg_cb FROM FITNESS.workouts;
member_id | workout_date | calories_burned | moving_avg_cb
----------- | -------------- | ------------------ | ---------------
m_192 | 2024-01-01 | 105 | 130.5
m_192 | 2024-01-03 | 156 | 110.0
m_192 | 2024-01-04 | 69 | 109.0
m_192 | 2024-01-10 | 102 | 120.0
m_192 | 2024-01-11 | 189 | 127.3
m_192 | 2024-01-12 | 91 | 145.0
m_192 | 2024-01-16 | 155 | 127.7
m_192 | 2024-01-19 | 137 | 133.7
m_192 | 2024-01-20 | 109 | 123.0
m_74 | 2024-02-10 | 374 | 385.0
...
SELECT member_id, workout_date, calories_burned, ... -- Use CURRENT ROW to avoid "look ahead" SUM(calories_burned) OVER( PARTITION BY member_id ORDER BY workout_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last_3_cb FROM FITNESS.workouts;
member_id | workout_date | calories_burned | moving_avg_cb | last_3_cb
----------- | -------------- | ------------------ | --------------- | -----------
m_192 | 2024-01-01 | 105 | 130.5 | 105
m_192 | 2024-01-03 | 156 | 110.0 | 261
m_192 | 2024-01-04 | 69 | 109.0 | 330
m_192 | 2024-01-10 | 102 | 120.0 | 327
m_192 | 2024-01-11 | 189 | 127.3 | 360
m_192 | 2024-01-12 | 91 | 145.0 | 382
m_192 | 2024-01-16 | 155 | 127.7 | 435
m_192 | 2024-01-19 | 137 | 133.7 | 383
m_192 | 2024-01-20 | 109 | 123.0 | 401
m_74 | 2024-02-10 | 374 | 385.0 | 374
...
Window Functions in Snowflake