[查询流失用户某等级的合成菜品分布] #BigQuery #MergeGarden

lost_users_level_compound.sql

SELECT /* 查询最高菜品等级 */
        user_pseudo_id,
        COUNT(user_pseudo_id) as user_count  /* 只要已解锁的最高菜品等级 */
    FROM
        `analytics_195246954.events_*` AS T,
        T.event_params
    WHERE
        event_name = 'af_compound_food'
        AND event_params.key = 'level'
        AND event_params.value.int_value = 2
        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' /* 修改为注册日期范围 */
                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 '20181220' AND '20181220' /* 修改为留存日期范围 */
        )
    GROUP BY user_pseudo_id /* 按伪用户id去重，每个id只保留最高菜品等级 */