Skip to content

Instantly share code, notes, and snippets.

@schacon

schacon/cohort.sql

Last active November 3, 2020 23:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save schacon/4667d1ffaf34a6f2d61dadf0aab77cf3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save schacon/4667d1ffaf34a6f2d61dadf0aab77cf3 to your computer and use it in GitHub Desktop.
Download ZIP
Cohort Month Base Values for BigQuery
Raw
cohort.sql
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('2017-10-01' AS DATE), CAST(max(start_time) AS DATE),
INTERVAL 1 MONTH) as date
from pg.chat_sessions)
) 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