Hi Camilla Antunes,

Thank you for your question.

If you do have a value, for example, zero cases reported, it would be present in the group by in the beginning and also included in your average calculation.

If let’s say, you have no cases reported for a date, and therefore, the date does not appear in our first group by in the beginning, and you want this date to be included and show 0, the solution would be:

• Generate a range of dates using the GENERATE_DATE_ARRAY() function in BigQuery
• Use a left join from this array of dates with our Covid cases table
• Replace NULL values with 0 values
• Then compute the moving average

I would be careful about the business question behind it and if it is expected to have a date missing or not (eg. No cases reported VS having 0 cases)