Day 31：Dune 进阶（JOIN 与子查询）+ 第一个增长分析 Dashboard

Day 31: Dune 进阶（JOIN 与子查询）+ 第一个增长分析 Dashboard

今日目标

完成三件事：

1. 掌握 Dune 中 JOIN 的常用模式（用户、交易、协议事件关联）
2. 能写子查询做分层统计（新用户、活跃、留存）
3. 产出第一个增长分析 Dashboard（至少 3 个图表）

今日学习重点

1. JOIN 思维（把分散数据拼成产品问题）

• LEFT JOIN：保留主表主体（例如用户集合）
• INNER JOIN：只看匹配记录（例如发生过交易的用户）
• 先定义“分析主体”，再决定 JOIN 方向，避免指标偏差

2. 子查询思维（先分层再汇总）

• 先用子查询拿到“每个地址第一笔交易时间”
• 再在外层聚合成日新增、周留存、转化率
• 复杂逻辑拆成 CTE，保证可读可复用

SQL 模板（可直接改后运行）

模板 1：每日活跃地址（DAU）

SELECT
  DATE_TRUNC('day', block_time) AS day,
  COUNT(DISTINCT "from") AS dau
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '30' day
GROUP BY 1
ORDER BY 1;

模板 2：新增地址（首交）

WITH first_tx AS (
  SELECT
    "from" AS wallet,
    MIN(block_time) AS first_tx_time
  FROM ethereum.transactions
  GROUP BY 1
)
SELECT
  DATE_TRUNC('day', first_tx_time) AS day,
  COUNT(*) AS new_wallets
FROM first_tx
WHERE first_tx_time >= NOW() - INTERVAL '30' day
GROUP BY 1
ORDER BY 1;

模板 3：新用户 7 日留存（示例）

WITH first_tx AS (
  SELECT
    "from" AS wallet,
    MIN(block_time) AS cohort_time
  FROM ethereum.transactions
  GROUP BY 1
),
cohort AS (
  SELECT
    wallet,
    DATE_TRUNC('day', cohort_time) AS cohort_day
  FROM first_tx
  WHERE cohort_time >= NOW() - INTERVAL '30' day
),
activity AS (
  SELECT DISTINCT
    c.wallet,
    c.cohort_day
  FROM cohort c
  JOIN ethereum.transactions t
    ON t."from" = c.wallet
   AND t.block_time >= c.cohort_day + INTERVAL '7' day
   AND t.block_time < c.cohort_day + INTERVAL '8' day
)
SELECT
  c.cohort_day,
  COUNT(DISTINCT c.wallet) AS cohort_size,
  COUNT(DISTINCT a.wallet) AS day7_retained,
  ROUND(COUNT(DISTINCT a.wallet) * 100.0 / NULLIF(COUNT(DISTINCT c.wallet), 0), 2) AS day7_retention_pct
FROM cohort c
LEFT JOIN activity a
  ON c.wallet = a.wallet
 AND c.cohort_day = a.cohort_day
GROUP BY 1
ORDER BY 1;

Day 31 Dashboard 骨架（最小可交付）

• 图表 1：DAU 趋势（30 天）
• 图表 2：新增地址趋势（30 天）
• 图表 3：7 日留存（按 cohort_day）

建议命名：

• Query 1: momoweb3_day31_dau
• Query 2: momoweb3_day31_new_wallets
• Query 3: momoweb3_day31_day7_retention

今日执行清单（2-3h）

• 跑通 3 条 SQL（先确保有结果，再优化口径）
• 组装 1 个 Day 31 Dashboard（3 图）
• 记录 2 条数据洞察（例如活跃与新增背离）
• 写 1 段面试表达（如何定义 Web3 增长指标）

面试表达（30 秒）

Day 31 我重点补了数据分析能力：通过 JOIN 与子查询把链上原始交易转成增长指标，搭建了包含 DAU、新增和 7 日留存的 Dashboard。核心不是写 SQL 本身，而是把“产品问题”映射成可解释的指标口径。

明日预告

Day 32：Dune 进阶（协议级表与多表关联），做 Aave/Uniswap 的协议行为拆解。