Query editor
-- kpi_retention_7d_30d_simple_avg_censor_fixed_one_queryWITH win AS (SELECT DATE '2025-08-13' AS start_day, DATE '2025-11-11' AS last_day),user_day AS (SELECTtaker,date_trunc('day', block_time) AS tx_dateFROM dex.trades, winWHERE block_time >= start_dayAND block_time < last_day + INTERVAL '1' DAYAND project='uniswap' AND blockchain='ethereum'),first_tx AS (SELECTtaker,MIN(tx_date) AS cohort_date,date_trunc('week', MIN(tx_date)) AS cohort_weekFROM user_dayGROUP BY taker),active AS (SELECT DISTINCT taker, tx_date FROM user_day),rel AS (-- 计算“相对周”索引(0,1,2,3,4)SELECTf.cohort_week,f.cohort_date,a.taker,CAST(date_diff('day', f.cohort_date, a.tx_date)/7 AS INTEGER) AS d_weekFROM first_tx fJOIN active a ON a.taker = f.takerWHERE a.tx_date >= f.cohort_date),-- 每个 cohort 在目标周的活跃用户agg7 AS (SELECT cohort_week, cohort_date, COUNT(DISTINCT taker) AS active_users_7FROM rel WHERE d_week = 1GROUP BY cohort_week, cohort_date),agg30 AS (SELECT cohort_week, cohort_date, COUNT(DISTINCT taker) AS active_users_30FROM rel WHERE d_week = 4GROUP BY cohort_week, cohort_date),cohort_size AS (SELECT cohort_week, cohort_date, COUNT(*) AS cohort_sizeFROM first_txGROUP BY cohort_week, cohort_date),-- 每个 cohort 的 7d / 30d 留存率(简单分子/分母)rates7 AS (SELECTc.cohort_week,c.cohort_date,CAST(a.active_users_7 AS DOUBLE)/NULLIF(c.cohort_size,0) AS r7FROM cohort_size cJOIN agg7 aON a.cohort_week = c.cohort_week AND a.cohort_date = c.cohort_date