[查询流失用户某等级的点击水壶分布] #BigQuery #MergeGarden

lost_users_level_tap_count.sql

SELECT
    A.user_pseudo_id,
    sum(A.tap_count) as tap_count
FROM
    (
        SELECT user_pseudo_id, event_params.value.int_value AS tap_count, event_timestamp
        FROM
        `analytics_195246954.events_*` AS T,
            T.event_params
        WHERE
            event_name = 'af_click_tap_button'
            AND event_params.key = 'af_count'
            AND _TABLE_SUFFIX BETWEEN '20181209' AND '20181210' /* 修改为从注册到要查询的留存日期范围 */
            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' /* 修改为留存日期范围 */
            )
    ) AS A,
    (
        SELECT /* 查询最高菜品等级 */
            user_pseudo_id, event_timestamp
        FROM
            `analytics_195246954.events_*` AS T,
            T.event_params
        WHERE
            event_name = 'af_click_tap_button'
            AND event_params.key = 'level'
            AND event_params.value.int_value = 2
            AND _TABLE_SUFFIX BETWEEN '20181219' AND '20181220' /* 修改为从激活到要查询的留存日期范围 */
    ) AS B
    WHERE A.user_pseudo_id = B.user_pseudo_id AND A.event_timestamp = B.event_timestamp
GROUP BY user_pseudo_id /* 按伪用户id去重 */