[查询活跃用户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 = '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