[查询流失用户的矿层进度人数分布]#BigQuery

layer_progress_of_lost_users.sql

SELECT
  max_layer,
  COUNT(user_id) as user_count
FROM (
  SELECT
      user_id,
      max(event_params.value.int_value) as max_layer  /* 只要已解锁的最大矿层数 */
  FROM
    `analytics_168921341.events_*` AS T,
    T.event_params
  WHERE
    event_name = 'activate_manto'
    AND event_params.key = 'manto_id'
    AND _TABLE_SUFFIX BETWEEN '20181113' AND '20181118' /* 修改为从注册到要查询的留存日期范围 */
    AND user_id IN (
      SELECT
        DISTINCT user_id
      FROM
        `analytics_168921341.events_*` AS T,
        T.event_params
      WHERE
        event_name = 'sign_up'
        AND geo.country = 'United States' /* 修改为指定国家 */
        AND platform = 'IOS'
        AND _TABLE_SUFFIX BETWEEN '20181113' AND '20181113' /* 修改为注册日期范围 */
      EXCEPT DISTINCT
      SELECT
        user_id
      FROM
        `analytics_168921341.events_*` AS T,
        T.event_params
      WHERE
        event_name = 'user_engagement'
        AND geo.country = 'United States' /* 修改为指定国家 */
        AND platform = 'IOS'
        AND _TABLE_SUFFIX BETWEEN '20181118' AND '20181118' /* 修改为留存日期范围 */
    )
  GROUP BY user_id /* 按用户id去重，每个id只保留最大矿层进度数 */
  )
GROUP BY max_layer
ORDER BY max_layer