[注册用户的矿层进度分布]#BigQuery

signup_layers_progress.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 '20181108' AND '20181109'
    AND user_id IN (
      SELECT
         DISTINCT user_id
      FROM
        `analytics_168921341.events_*` AS T,
        T.event_params
      WHERE
        event_name = 'tutorial_complete'
        AND event_params.key = 'guide_id'
        AND event_params.value.int_value = 51
        AND geo.country = 'United States' /* 修改为指定国家 */
        AND platform = 'ANDROID'
        AND _TABLE_SUFFIX BETWEEN '20181108' AND '20181108' /* 修改为events数据范围 */ 
    )
  GROUP BY user_id /* 按用户id去重，每个id只保留最大矿层进度数 */
  )
GROUP BY max_layer
ORDER BY max_layer