24小时留存计算

表名为 src_table, 表中只有两列，玩家ID和游戏时间戳 user_id, event_timestamp, 求玩家24小时的留存。从新增开始每24小时算一天。

user_id	event_timestamp
a	2022-08-01T00:01:00Z
a	2022-08-01T00:05:00Z
b	2022-08-01T00:06:00Z

with t1 as (
  -- 去重
  select distinct a.user_id, event_timestamp
  from src_table a
), t2 as (
  -- 求出首次出现时间
  select user_id, min(event_timestamp) as first_ts
  from src_table
  group by 1
), t3 as (
  select distinct a.user_id, date(first_ts) as first_date, TIMESTAMP_DIFF(event_timestamp, first_ts, day) as day
  from t1 a
  inner join t2 b
  on a.user_id = b.user_id
)
 select first_date, count(case when day = 0 then 1 end) as new_users,
  count(case when day = 1 then 1 end) as day2_active_users,
  count(case when day = 2 then 1 end) as day3_active_users,
  count(case when day = 6 then 1 end) as day7_active_users,
from `t3`
group by 1
order by 1
;