[查询注册用户某等级的商店购买菜品分布+最高等级] #BigQuery #MergeGarden

users_level_shop_buy_count_maxLevel.sql

SELECT /* 归并购买相同的菜品等级，按用户计数 */
  A.user_pseudo_id,
  COUNT(A.user_pseudo_id) as user_count
FROM (
    SELECT /* 查询最高菜品等级 */
        user_pseudo_id, event_timestamp
    FROM
        `analytics_195246954.events_*` AS T,
        T.event_params
    WHERE
        event_name = 'af_shop_buy_food'
        AND event_params.key = 'af_food_id'
        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 '20181209' AND '20181209' /* 修改为激活日期范围 */
            )
        AND _TABLE_SUFFIX BETWEEN '20181219' AND '20181220' /* 修改为从激活到要查询的留存日期范围 */
) AS A,
(
    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 '20181219' AND '20181220' /* 修改为从激活到要查询的留存日期范围 */
    group by user_pseudo_id
) AS B
WHERE A.user_pseudo_id = B.user_pseudo_id AND B.max_level = 6
GROUP BY user_pseudo_id