####[查询留存用户stage.progress.count] #BigQuery #MergeGarden

stage_progress_count.sql

SELECT stage, count(user_pseudo_id) as count FROM
(
    SELECT C.user_pseudo_id, C.event_timestamp, D.stage, E.wave FROM
      (
          SELECT
              A.user_pseudo_id, max(A.event_timestamp) as event_timestamp
          FROM
          (
              SELECT
                  user_pseudo_id,
                  event_timestamp,
                  event_params.value.int_value as stage
              FROM
                  `analytics_195246954.events_*` AS T,
                  T.event_params
              WHERE
                  event_name = 'af_stage_progress'
                  AND event_params.key = 'af_stage'
                  AND _TABLE_SUFFIX BETWEEN '20181219' AND '20181220' /* 修改为从注册到留存日期范围 */
                  AND user_pseudo_id IN (
      				SELECT /* 查询新用户 */
                  DISTINCT user_pseudo_id
              FROM
                  `analytics_195246954.events_*` AS T,
                  T.event_params
              WHERE
                  event_name = 'first_open'
                  AND geo.country = 'United States' /* 修改为指定国家 */
                  AND platform = 'ANDROID'
                  AND _TABLE_SUFFIX BETWEEN '20181219' AND '20181219' /* 修改为激活日期范围 */
              INTERSECT DISTINCT /* 只包含留存用户 */
              SELECT
                  user_pseudo_id
              FROM
                  `analytics_195246954.events_*` AS T,
                  T.event_params
              WHERE
                  event_name = 'user_engagement'
                  AND geo.country = 'United States' /* 修改为指定国家 */
                  AND platform = 'ANDROID'
                  AND _TABLE_SUFFIX BETWEEN '20181220' AND '20181220' /* 修改为留存日期范围 */
                  )
          ) as A,
          (
              SELECT
                  user_pseudo_id,
                  event_timestamp,
                  event_params.value.int_value as wave
              FROM
                  `analytics_195246954.events_*` AS T,
                  T.event_params
              WHERE
                  event_name = 'af_stage_progress'
                  AND event_params.key = 'af_wave'
          ) as B
          WHERE
          A.user_pseudo_id = B.user_pseudo_id
          AND A.event_timestamp = B.event_timestamp
          GROUP BY A.user_pseudo_id
      ) AS C,
      (
          SELECT
              user_pseudo_id,
              event_timestamp,
              event_params.value.int_value as stage
          FROM
              `analytics_195246954.events_*` AS T,
              T.event_params
          WHERE
              event_name = 'af_stage_progress'
              AND event_params.key = 'af_stage'
      ) as D,
      (
          SELECT
              user_pseudo_id,
              event_timestamp,
              event_params.value.int_value as wave
          FROM
              `analytics_195246954.events_*` AS T,
              T.event_params
          WHERE
              event_name = 'af_stage_progress'
              AND event_params.key = 'af_wave'
      ) as E
      WHERE C.user_pseudo_id = D.user_pseudo_id AND C.user_pseudo_id = E.user_pseudo_id
      AND C.event_timestamp = D.event_timestamp AND C.event_timestamp = E.event_timestamp
)
GROUP BY stage
ORDER BY stage