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

users_level_shop_buy_count.sql

SELECT /* 归并购买相同的菜品等级，按用户计数 */
  user_pseudo_id,
  COUNT(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 event_params.value.int_value = 2
        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' /* 修改为从激活到要查询的留存日期范围 */
)
GROUP BY user_pseudo_id