Covid-19 has been a perfect example for the need to use Moving Averages. The nature of the pandemic means that the number of cases and deaths each day varies, sometimes drastically, which essentially creates noise.
A common practice is to smooth a time series to basically eliminate a lot of the daily noise and create a smooth trend.
The most basic technique is called a Simple Moving Average, and is calculated by taking the mean of the past n data points.
For this example we have used one of the open data sets for Covid-19 Dataset Details. The actual dataset is located here bigquery-public-data.covid19_open_data_eu.covid19_open_data
From the below query you will see the use of a window function being used with AVG
, pay particular attention to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
this is what gives us the 7 day moving average.