When aggregating data that jumps around by date, you may want a calendar table.
You can create a calendar table in Google BigQuery with the following
- GENERATE_DATE_ARRAY()" creates a calendar array, and "UNNEST()" expands the array to rows to make it a table.
SELECT * FROM UNNEST( GENERATE_DATE_ARRAY( '2019-01-01', '2019-12-31', INTERVAL 1 DAY) ) AS date
You can change the date interval with INTERVAL <INT> <DATE_PART>.
For example, if you want a calendar for the first day of each month, use "INTERVAL 1 MONTH".
The argument "INTERVAL 1 DAY" in the above example is the default value and does not have to be written.
