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

Revisions

  1. schacon revised this gist Nov 3, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions cohort.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -3,8 +3,8 @@ select month, months_out from (
    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),
    GENERATE_DATE_ARRAY(CAST([STARTING_DATE] AS DATE), CAST(max(date_field) AS DATE),
    INTERVAL 1 MONTH) as date
    from pg.chat_sessions)
    from [SOME_TABLE_WITH_A_DATE_FIELD])
    ) as month
    ) CROSS JOIN UNNEST(months) as months_out
  2. schacon created this gist Nov 3, 2020.
    10 changes: 10 additions & 0 deletions cohort.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,10 @@
    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