Moving averages

Moving averages in Snowflake are an excellent way to discover patterns in your data.

For example, the chart below shows the daily temperature in London as points, and a 30 day moving average as a line. Plotting both lets us see the variation of temperature day-by-day, without obscuring longer-term patterns.

SELECT
  DATE, TEMP,
  AVG(TEMP) OVER (
    ORDER BY DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) "30_DAY_AVG_TEMP"
FROM PUBLIC.LONDON_WEATHER

Step-by-Step

Moving averages rely on the use of Window Functions, which you can read more about here.  

To find a running total we'll use the AVG window function:

AVG( <expr1> ) OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ] )

• expr1 is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
• expr2 is the optional expression to partition by.
• expr3 is the optional expression to order by within each partition. (This does not control the order of the entire query output.)

Or, written for a human:

AVG(column) OVER (PARTITION BY col_to_group_by, ORDER BY column_to_order_by)

In the following example we'll take the running total of hours of Netflix I watched in a given week:

SELECT 
  DATE_TRUNC('WEEK', START_TIME) WEEK,
  DATE_PART('WEEKDAY', START_TIME) DAY, 
  SUM(DURATION) / (60 * 60) DAILY_HOURS_WATCHED 
FROM 
  PUBLIC.NETFLIX 
WHERE 
  WEEK = '2018-11-26'
GROUP BY WEEK, DAY
ORDER BY WEEK, DAY

Now if I want to find the moving 3-day average of hours watched that week, I can do the following:

SELECT
  DAY, DAILY_HOURS_WATCHED,
  AVG(DAILY_HOURS_WATCHED) OVER (
    ORDER BY DAY ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) "3_DAY_AVG"
FROM ONE_WEEK

We can see our new 3_DAY_AVG update as the average between the current row and the 2 previous rows.

To check we can make sure that day 2 is the average of Day 0, Day 1, and Day 2 hours watched:

SELECT (4.413 + 1.467 + 0.656) / 3 AS day_2_result

This example had no partition, but if we wanted to compare my viewing habits across two different weeks, we would need to PARTITION BY week:

SELECT 
  DATE_TRUNC('WEEK', START_TIME) WEEK,
  DATE_PART('WEEKDAY', START_TIME) DAY, 
  SUM(DURATION) / (60 * 60) DAILY_HOURS_WATCHED 
FROM 
  PUBLIC.NETFLIX 
WHERE 
  WEEK IN ('2018-11-19', '2018-11-26')
GROUP BY WEEK, DAY
ORDER BY WEEK, DAY

And now to find the running total across the two weeks

Now we see the RUNNING_TOTAL restart for the 2nd week.  

How We Built This

This page was built using Count, the first notebook built around SQL. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.

This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.

SELECT 
  WEEK, DAY, DAILY_HOURS_WATCHED,
  AVG(DAILY_HOURS_WATCHED) OVER(
    PARTITION BY WEEK ORDER BY DAY ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) "7_DAY_AVG"
FROM TWO_WEEKS
ORDER BY WEEK, DAY