Window frames

1. Window frames

We've mostly worked with two different types of window frames; let's see what we can do to spice things up a bit.

2. Window frames

The first window frame that we operated on was typically made up of the entire table. For example, we could rank all records in a table, or find the average for the entire table, what we're doing here.

3. Window frames

Then, we were started to use partitions. These allowed us to create separate windows based on some field; here, that's the `member_id` field.

4. Dynamic window frames

What if we'd like to find out how a member's average calories burned changes after each workout? To do this, we'd need a "sliding" window frame. In this example, we're partitioning records by `member_id`, then we're finding a running average of `calories_burned`. The first record has a window frame size of 1, and the average is 105. Then next record has a window frame size of two, made up of itself and the second record. The running average is now 130.5. This continues on until the last record for member 192. But, how can we do this?

5. Finding a running calculation

Snowflake provides the `ROWS BETWEEN` syntax to create a dynamic, or "sliding", window frame. To use `ROWS BETWEEN`, we need to specify a starting and ending row. To create a running average like we saw in the last output, we'll add ` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` after the `ORDER BY` statement. This creates a window from the first row in the partition until the current row. Note, we're using the term "running", not "rolling". We'll look at "rolling" calculations in a bit. We'll still need to pass a record to a function, like `AVG` and `ORDER BY` a field; this is what allows us to properly build the window frame. If we'd like to create a window from the current row until the last row in a partition, we could use the syntax `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`.

6. Running total of calories burned

Here, we're using `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` to find the running total of calories burned for each member. Let's break it down a bit more; we're passing `calories_burned` to the `SUM` function. Inside `OVER`, we `PARTITION BY member_id`, then `ORDER BY workout_date`. Including `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` ensures that we calculate an updated total of calories burned for each member after every workout.

7. CURRENT ROW AND UNBOUNDED FOLLOWING

If we were to use `CURRENT ROW AND UNBOUNDED FOLLOWING`, our results in the `left_to_burn` column would "count down", like this.

8. Calorie-burning trends

To find the running average of calories burned for each gym member, we'll replace the `SUM` function with `AVG`. The syntax within `OVER` will remain the same. Our window frame will slide for each member to calculate a running average of calories burned after each workout.

9. Calorie-burning trends

Our result set matches what we saw earlier. We've successfully created a running average of calories burned for each member. This would be a great metric to include in a user-facing dashboard to help motivate members.

10. Let's practice!

Alright, let's practice building our own window frames!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.