用户活跃度数据集（active users）

• SQL：schema/clickhouse/derived/014_active_users.sql
• 依赖：{db}.transactions（原始表，见 sql/schema.sql / docs/design/0001-architecture.md §4.3）
• 不依赖：{db}.traces（另行接入中，本数据集完全不用它）

1. 这个数据集是什么Copy Anchor Link

面向"每日活跃地址数（DAU）/ 新增地址 / 周月活跃（WAU/MAU）/ D1-D7-D30 留存"这一类 增长分析查询，按 已收盘的 UTC 自然日 维度预计算好，避免内部团队每次都要现写一遍 "排除系统地址 + 排除协议自动交易 + 去重版本 + 处理 tombstone"这一整套口径。

产出三张表：address_first_seen、daily_active_addresses、user_retention_cohorts。

2. 口径定义（三张表统一使用，务必保持一致）Copy Anchor Link

一个地址在某天算"活跃"，当且仅当它是当天至少一笔 未删除（is_deleted=0）交易的 from 方，且排除：

其余全部交易类型（legacy 0x0、access-list 0x1、EIP-1559 0x2、EIP-7702 0x4，以及未来任何 未识别类型）都计入活跃——对齐架构文档 §1.1"未知类型不能报错/不能被静默丢弃"的原则。

这段过滤条件在 SQL 文件的三处 refresh 语句里逐字重复，修改口径时三处要一起改。

3. 为什么用"闭区间日 + 幂等 refresh"，不用 insert-trigger 物化视图Copy Anchor Link

{db}.transactions 是 ReplacingMergeTree(version, is_deleted)：同一笔交易可能被 重复插入（backfill 断点续跑），重组还会写入 is_deleted=1 的 tombstone。"某地址今天 是否活跃""某地址第一次出现在哪天""某地址是否留存"都是对一整天做集合运算的事实， 不是逐行可以下结论的事——insert-trigger MV（erc20_transfers 那种模式）一次只看一行, 既无法感知"这是重复插入"，也无法在 tombstone 到来时把之前的计数撤回。

所以三张表都用同一种模式：由脚本 / cron 对每个已收盘的 UTC 日，重跑 SQL 文件底部的 参数化 INSERT ... SELECT（用 ClickHouse 原生查询参数 {name:Type}，不需要 sed）。 "已收盘"指这一天的墙钟时间已经完全过去——本链 pruned 状态窗口约 15 分钟 （见 docs/design/0001-architecture.md §2），实际可能触达这个深度的重组早已不存在。

注意："已收盘"只回答"这天的数据以后还会不会被重组改写"，不回答"这天的数据是否已经 完整抽取进 transactions"——这是两件事。 摄入完整性依赖 follow 模式追平链头（见 docs/design/0001-architecture.md §1.1，这条验收标准目前仍是未勾选的 [ ]），如果 follow 因为重启、RPC 抖动等原因落后，UTC 零点触发 refresh 时当天可能还没抽完。本 PR 的 SQL 不做这层校验；运营脚本在对某天跑 refresh 前，应额外确认摄入进度已经越过 当天 24:00 UTC（例如对照 _indexer_progress（见 docs/design/0001-architecture.md §3.5）里 follow 模式最新完成 segment 对应的区块时间戳），而不能只看墙钟时间是否 已经过去。address_first_seen 一旦写入某天就不会再改（见下方"已知局限"），用不完整 的数据跑出的一天会被永久错记。

每条语句都从 {db}.transactions FINAL 读（tombstone 和重复版本已经折叠掉），且 重跑同一个已收盘日是安全的：

• address_first_seen：refresh 语句本身只会挑出"表里还没有的地址"（NOT IN 子查询）， 重跑不会插入第二条同地址的行。前提是按时间顺序、从创世日开始、逐日不跳着跑—— 见下面"安装与补算"里的强制顺序。
• daily_active_addresses：uniqExact 是精确去重（不是 uniq/uniqHLL12 那种近似 基数),它的部分状态可以精确合并——同一天两次 refresh 会插入两行状态，但 uniqExactMerge 合并两个"同一批地址"的状态等于对同一个集合做并集，结果不变（已在 下面第 5 节用真实数据验证：重跑后 DAU 数字分毫不差）。这也是"一天一行小状态"就能 同时服务日/周/月活的原因：GROUP BY toStartOfWeek(date) / toStartOfMonth(date) 直接 合并若干天的状态即可得到精确的周活/月活，不需要重新扫 transactions。
• user_retention_cohorts：(cohort_date, horizon_days) 只有在 cohort_date + horizon_days 也已收盘时才能算，重跑用的是同样的已收盘输入，产出内容逐字节相同, ReplacingMergeTree 里哪个版本"赢"都无所谓。

4. 表结构Copy Anchor Link

-- 见 014_active_users.sql 顶部完整注释
address_first_seen(address, first_seen_date, first_seen_block, first_seen_tx_hash, version, is_deleted)
    ENGINE = ReplacingMergeTree(version, is_deleted) ORDER BY address

daily_active_addresses(date, active_state AggregateFunction(uniqExact, FixedString(20)))
    ENGINE = AggregatingMergeTree ORDER BY date

user_retention_cohorts(cohort_date, horizon_days, cohort_size, retained_count, version, is_deleted)
    ENGINE = ReplacingMergeTree(version, is_deleted) ORDER BY (cohort_date, horizon_days)

5. 安装与补算Copy Anchor Link

1. 建表（替换 {db}，如 robinhood）：

   Copy Text

   sed 's/{db}/robinhood/g' schema/clickhouse/derived/014_active_users.sql \
     | # 只取三个 CREATE TABLE 语句，见下方"HTTP 接口一次只能发一条语句"的说明

   三张表的 CREATE TABLE IF NOT EXISTS 都是幂等的，可以随时重复执行。

2. 强制顺序回填历史：从链的创世日开始，按日期从早到晚、一天不落地依次对每个 已收盘日跑三条 refresh 语句（先 address_first_seen，再 daily_active_addresses，最后视需要跑 user_retention_cohorts 的三个 horizon）。 这个顺序是 address_first_seen 正确性的前提——见第 3 节的"前提"。生产环境建议写一个 小循环脚本按天推进，出错就停（fail-closed），不要跳过失败的日期继续往后跑。

3. 历史补完后，转成每日 cron：每天 UTC 0 点后，对"昨天"跑 address_first_seen + daily_active_addresses；同时对 昨天 - 1 / - 7 / - 30 三个 cohort_date（只要对应的 cohort_date + horizon 也已收盘）跑 user_retention_cohorts。

   Copy Text

   clickhouse-client --param_target_date='2026-09-24' -q "$(sed -n '/INSERT INTO {db}\.address_first_seen/,/GROUP BY t\.from;/p' 014_active_users.sql | sed 's/{db}/robinhood/g')"
   clickhouse-client --param_target_date='2026-09-24' -q "$(sed -n '/INSERT INTO {db}\.daily_active_addresses/,/NOT IN (0x68, 0x69);/p' 014_active_users.sql | sed 's/{db}/robinhood/g' | tail -n +1)"
   clickhouse-client --param_cohort_date='2026-09-24' --param_horizon=1 -q "..."  # 同理，horizon 再跑 7、30

   HTTP 接口一次只能发一条语句（同 003_backfill.sql 的说明），批量跑时用 clickhouse-client --queries-file 或逐条 curl --data-binary。

6. 查询示例Copy Anchor Link

-- 日活 / 周活 / 月活（同一份状态，不同粒度合并即可）
SELECT date, uniqExactMerge(active_state) AS dau
FROM robinhood.daily_active_addresses GROUP BY date ORDER BY date;

SELECT toStartOfWeek(date) AS week, uniqExactMerge(active_state) AS wau
FROM robinhood.daily_active_addresses GROUP BY week ORDER BY week;

SELECT toStartOfMonth(date) AS month, uniqExactMerge(active_state) AS mau
FROM robinhood.daily_active_addresses GROUP BY month ORDER BY month;

-- 每日新增地址
SELECT first_seen_date, count() AS new_addresses
FROM robinhood.address_first_seen FINAL WHERE is_deleted = 0
GROUP BY first_seen_date ORDER BY first_seen_date;

-- D1 / D7 / D30 留存率
SELECT cohort_date, horizon_days, cohort_size, retained_count,
       retained_count / nullIf(cohort_size, 0) AS retention_rate
FROM robinhood.user_retention_cohorts FINAL WHERE is_deleted = 0
ORDER BY cohort_date, horizon_days;

7. 验证结果Copy Anchor Link

在本地临时库（active_users_scratch_wt38，验证完已删除）用真实 Robinhood 链数据验证。 区块范围特意选在一次 UTC 零点前后（71779925–71784725，4800 块），保证样本横跨两个 自然日：2026-09-24（19122 笔交易）与 2026-09-25（40093 笔交易）。

日活：派生表 vs. 对 transactions FINAL 直接 GROUP BY（同一份过滤条件，两套独立 写法），逐位精确匹配：

两天状态合并后的"周活口径"验证同样精确匹配：派生表 uniqExactMerge 得到 9204， 独立对两天数据整体 uniqExact(from) 也是 9204，验证了跨天状态合并（WAU/MAU 用的 就是这个机制)的正确性,而不只是单天。

新增地址（address_first_seen）：09-24 当天 5459（等于当天全部活跃地址，因为这是 本次追踪窗口的起点，全部"首次出现"），09-25 当天 3745（意味着 5626-3745=1881 个地址是 09-24 就活跃过的回头地址）。

D1 留存（真实数据）：cohort_date=2026-09-24（cohort_size=5459），horizon=1 天, 派生表算出 retained_count=1881；独立写法——对 09-24 活跃地址集合与 09-25 活跃地址集合 取交集（INNER JOIN ... USING from）——同样是 1881，精确匹配，且与上面"回头地址" 的推算完全自洽。

幂等性（真实数据实测）：对 09-24 重复执行 address_first_seen 的 refresh 语句， 行数不变（仍是 5459，没有产生重复地址）；对 09-24 重复执行 daily_active_addresses 的 refresh 语句后,该日期下的物理行数从 1 变成 2,但 uniqExactMerge 查询结果保持 5459 不变——验证了"同一天重跑不会重复计数"的设计 声明,不是纸面推导。

D7 留存（合成数据）：本地 4800 块窗口只能跨一次日界，凑不出真实的 7 天间隔样本; 用相同 active_users_scratch_wt38 库里手工插入的 3 笔合成交易验证同一套 SQL 模板 （仅 {horizon} 参数不同,逻辑代码路径与已验证的 D1 完全一致）：地址 A 在 2025-01-01 和 2025-01-08 各发一笔交易,地址 B 只在 2025-01-01 发一笔。跑 horizon=7 后得到 cohort_size=2, retained_count=1,与预期（A 留存、B 未留存)一致。 D30 逻辑代码路径与 D1/D7 完全相同（只是 {horizon} 参数从 1/7 换成 30),未单独起 30 天窗口重复验证。

8. 已知局限Copy Anchor Link

• address_first_seen 的正确性依赖"从创世日开始逐日不跳着跑"这一操作纪律（见第 5 节),SQL 本身无法在运行时校验这个前提是否被满足——如果需要更强的保证,后续可以加一张 "已处理日期"的进度表,在 refresh 语句里做存在性检查后 fail-closed,目前未实现。
• "已收盘日"目前仅由墙钟时间 + 重组窗口判定（见第 3 节),不核对 follow 摄入进度是否 已经越过当天日终——如果 follow 落后,UTC 零点跑 refresh 会在数据不完整时把这天标记 为"已处理",且 address_first_seen 没有事后纠正机制。目前依赖运营脚本手工核对 _indexer_progress（docs/design/0001-architecture.md §3.5),SQL 本身不做这个校验, 也未实现。
• 系统地址 / 协议自动交易的排除规则目前只覆盖了本次真实区块窗口里实际出现的类型 (0x6a 系统内部、0x68/0x69 retryable);如果链上出现新的"协议自动生成、from 是真实地址但不代表用户主动行为"的交易类型,需要人工评估是否要加入排除列表。