Skip to content

Instantly share code, notes, and snippets.

@andy51002000

andy51002000/retention-any-period.sql

Created April 6, 2020 13:18
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andy51002000/d02d5808beda97c3c6e8fcf4407153ee to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andy51002000/d02d5808beda97c3c6e8fcf4407153ee to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. andy51002000 created this gist Apr 6, 2020.
    8 changes: 8 additions & 0 deletions retention-any-period.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,8 @@
    WITH analytics_data AS (
    SELECT user_pseudo_id,
    event_timestamp,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*30 AS period # 3600*1000*1000*24 表示一天
    FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE _table_suffix BETWEEN '20180731' AND '20180829'
    )