Skip to content

Instantly share code, notes, and snippets.

@schacon
Last active November 3, 2020 23:38
Show Gist options
  • Save schacon/4667d1ffaf34a6f2d61dadf0aab77cf3 to your computer and use it in GitHub Desktop.
Save schacon/4667d1ffaf34a6f2d61dadf0aab77cf3 to your computer and use it in GitHub Desktop.
Cohort Month Base Values for BigQuery
select month, months_out from (
select month,
GENERATE_ARRAY(0, DATE_DIFF(CURRENT_DATE(), month, MONTH)) as months,
from UNNEST(
(select
GENERATE_DATE_ARRAY(CAST([STARTING_DATE] AS DATE), CAST(max(date_field) AS DATE),
INTERVAL 1 MONTH) as date
from [SOME_TABLE_WITH_A_DATE_FIELD])
) as month
) CROSS JOIN UNNEST(months) as months_out
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment