cohort4.sql

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
  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'
)
SELECT * from week_1_users JOIN week_0_users USING(user_pseudo_id)