ToddKerpelman

SELECT DISTINCT user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
  event_name = 'first_open'
  AND event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00"))
  AND event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00"))
  AND _TABLE_SUFFIX BETWEEN '20180731' AND '20180808'

ToddKerpelman

SELECT DISTINCT user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE event_name = 'first_open'
  AND _TABLE_SUFFIX BETWEEN '20180801' AND '20180807'

ToddKerpelman

SELECT DISTINCT user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00")) 
AND  event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-15 00:00:00", "-7:00")) 
AND _TABLE_SUFFIX BETWEEN '20180807' AND '20180815'
AND user_pseudo_id IN ( 
  SELECT DISTINCT user_pseudo_id
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE event_name = 'first_open'
  AND event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) 

ToddKerpelman

WITH week_1_users AS (
  SELECT DISTINCT user_pseudo_id
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00")) 
    AND  event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-15 00:00:00", "-7:00")) 
    AND _TABLE_SUFFIX BETWEEN '20180807' AND '20180815'
), 
week_0_users AS (
  SELECT DISTINCT user_pseudo_id

ToddKerpelman

WITH week_3_users AS (
  SELECT DISTINCT user_pseudo_id
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-22 00:00:00", "-7:00")) 
    AND  event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-29 00:00:00", "-7:00")) 
    AND _TABLE_SUFFIX BETWEEN '20180821' AND '20180829'
),
week_2_users AS (
  SELECT DISTINCT user_pseudo_id
  FROM `firebase-public-project.analytics_153293282.events_*`

ToddKerpelman

SELECT 
 week_0_cohort / week_0_cohort AS week_0_pct,
 week_1_cohort / week_0_cohort AS week_1_pct,
 week_2_cohort / week_0_cohort AS week_2_pct,
 week_3_cohort / week_0_cohort AS week_3_pct
FROM (
  WITH week_3_users AS (
    SELECT DISTINCT user_pseudo_id
    FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-22 00:00:00", "-7:00")) 

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)
SELECT DISTINCT user_pseudo_id
FROM analytics_data
WHERE event_name = 'first_open'

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)

SELECT week_0_cohort / week_0_cohort AS week_0_pct,
 week_1_cohort / week_0_cohort AS week_1_pct,

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    -- This value is different
    UNIX_MICROS(TIMESTAMP("2018-07-29 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  -- These values have changed                          
  WHERE _table_suffix BETWEEN '20180728' AND '20180827'
)
SELECT week_0_cohort / week_0_cohort AS week_0_pct,

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    app_info.version AS app_version,   -- This is new!
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)

SELECT week_0_cohort / week_0_cohort AS week_0_pct,