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'
  AND user_pseudo_id IN ( 
    -- Anybody who's ever encountered a level_retry_quickplay event!
    SELECT DISTINCT(user_pseudo_id) FROM 

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'
  AND user_pseudo_id IN (  
    SELECT DISTINCT(user_pseudo_id) FROM 
      (SELECT user_pseudo_id,
             (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = "initial_extra_steps") AS initial_steps

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'
  AND user_pseudo_id IN ( 
    -- Anybody who received 20 initial extra steps
    SELECT DISTINCT(user_pseudo_id) FROM 

ToddKerpelman

SELECT DISTINCT(user_pseudo_id) FROM 
  (SELECT user_pseudo_id,
      (SELECT value.string_value 
        FROM UNNEST(user_properties) 
        WHERE key = "initial_extra_steps") AS initial_steps
    FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  )
WHERE initial_steps = "20"

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, device.mobile_model_name,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*3 AS one_week_micros   -- Note the *3 at the end here
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
)
...rest of the query goes here

ToddKerpelman

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name, 
    device.mobile_model_name, -- This parameter 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'
    AND device.mobile_model_name LIKE "iPad Pro%"  -- This is also new
)
... Rest of the query goes here

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,

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, 
    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, 
    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'