如何在BigQuery中按user_pseudo_id统计first_login与game_launch间的addressable_load事件数

要统计每个user_pseudo_id在first_login与game_launch事件之间发生的addressable_load事件总数，你可以通过以下分步查询实现：

问题分析 #

你原有的查询由于分组维度包含event_name和event_timestamp，无法正确聚合用户级别的事件时间边界，需要先提取每个用户的first_login和game_launch时间节点，再筛选统计中间的目标事件。

完整查询语句 #

WITH user_events AS (
  -- 筛选目标事件，减少数据处理量
  SELECT
    user_pseudo_id,
    user_id,
    event_name,
    event_timestamp,
    DATETIME(timestamp_micros(event_timestamp), "UCT") AS date_time
  FROM `aaa-96dd2.analytics_272019935.events_*`
  WHERE event_date = "20230405"
    AND event_name IN ('first_login', 'game_launch', 'addressable_load')
),
user_time_boundaries AS (
  -- 提取每个用户的first_login和后续最早的game_launch时间戳
  SELECT
    user_pseudo_id,
    user_id,
    MIN(CASE WHEN event_name = 'first_login' THEN event_timestamp END) AS first_login_ts,
    MIN(CASE 
          WHEN event_name = 'game_launch' 
               AND event_timestamp > MIN(CASE WHEN event_name = 'first_login' THEN event_timestamp END)
          THEN event_timestamp 
        END) AS game_launch_ts
  FROM user_events
  GROUP BY user_pseudo_id, user_id
  -- 仅保留同时存在first_login和对应game_launch的用户
  HAVING first_login_ts IS NOT NULL AND game_launch_ts IS NOT NULL
)
-- 统计时间区间内的addressable_load事件数量
SELECT
  utb.user_pseudo_id,
  utb.user_id,
  COUNT(ue.event_name) AS addressable_load_count
FROM user_time_boundaries utb
JOIN user_events ue
  ON utb.user_pseudo_id = ue.user_pseudo_id
  AND utb.user_id = ue.user_id
  AND ue.event_name = 'addressable_load'
  AND ue.event_timestamp BETWEEN utb.first_login_ts AND utb.game_launch_ts
GROUP BY utb.user_pseudo_id, utb.user_id
ORDER BY addressable_load_count DESC;

查询逻辑说明 #

1. user_events CTE：仅筛选2023年4月5日的三类目标事件，避免无关数据干扰，同时保留事件的时间戳和用户标识。
2. user_time_boundaries CTE：按用户分组，提取每个用户最早的first_login时间戳，以及该时间之后最早的game_launch时间戳；通过HAVING子句过滤掉缺少任一关键事件的用户。
3. 最终统计：将用户时间边界表与事件表关联，筛选出时间落在first_login和game_launch之间的addressable_load事件，按用户分组统计总数。

内容的提问来源于stack exchange，提问作者prasad