adityawarmanfw

  SELECT episode, 
         event_name,
         COUNT(user_id) AS events,
         COUNT(DISTINCT user_id) AS users
    FROM audio_event
GROUP BY episode

adityawarmanfw

WITH 
first_open AS (
    SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") as first_open_dt, 
           user_pseudo_id
      FROM `firebase-public-project.analytics_153293282.events_*`
     WHERE event_name = "first_open" 
       AND device.operating_system = "ANDROID"
), app_remove as (
    SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") as remove_dt, 
           user_pseudo_id

adityawarmanfw

WITH 
first_open AS (
    SELECT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") AS first_open_dt, 
           user_pseudo_id
      FROM `firebase-public-project.analytics_153293282.events_*`
     WHERE _TABLE_SUFFIX BETWEEN "20180801" AND "20180930"
       AND event_name = "first_open" 
),
active as (
    SELECT DISTINCT DATE(TIMESTAMP_MICROS(event_timestamp),"Asia/Jakarta") AS active_dt,