####[查询流失用户，某等级下最高stage.progress] #BigQuery #MergeGarden

lost_users_level_stage_progress.sql

SELECT C.user_pseudo_id, 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 '20190324' AND '20190325' /* 修改为从注册到留存日期范围 */
            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 '20190324' AND '20190324' /* 修改为注册日期范围 */
				EXCEPT DISTINCT /* 排除留存用户 */
				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 '20190325' AND '20190325' /* 修改为留存日期范围 */
            )
    ) 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,
(
    SELECT /* 查询最高菜品等级 */
        user_pseudo_id,
        max(event_params.value.int_value) as max_level  /* 只要已解锁的最高菜品等级 */
    FROM
        `analytics_195246954.events_*` AS T,
        T.event_params
    WHERE
        event_name = 'level_up'
        AND event_params.key = 'level'
        AND _TABLE_SUFFIX BETWEEN '20190324' AND '20190325' /* 修改为从激活到要查询的留存日期范围 */
    GROUP BY user_pseudo_id /* 按伪用户id去重，每个id只保留最高菜品等级 */
) as F
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
AND F.max_level = 10  AND C.user_pseudo_id = F.user_pseudo_id
ORDER BY D.stage, E.wave