Exercise

Calculating moving averages

Instead of looking at a running total from the beginning of time until now, management would like to see the average number of incidents over the past 7 days--that is, starting 6 days ago and ending on the current date. Because this is over a specified frame which changes over the course of our query, this is called a moving average.

SQL Server does not have the ability to look at ranges of time in window functions, so we will need to assume that there is one row per day and use the ROWS clause.

Instructions

100 XP
  • Fill in the correct window function to perform a moving average starting from 6 days ago through today (the current row).
  • Fill in the window frame, including the ROWS clause, window frame preceding, and window frame following.