Query editor
-- repeat_rate_90d_censor_fixed_fixedWindowWITH user_day AS (SELECT DISTINCTtaker,date_trunc('day', block_time) AS tx_dateFROM dex.tradesWHERE block_time >= DATE '2025-08-13'AND block_time < DATE '2025-11-12'AND project = 'uniswap'AND blockchain = 'ethereum'),last_day AS (SELECT DATE '2025-11-11' AS last_day -- 锚定窗口终点(UTC)),first_tx AS (SELECT taker, MIN(tx_date) AS cohort_dateFROM user_dayGROUP BY taker),eligible AS (-- 预留 ≥30 天观察期:剔除太“新”的首日SELECT f.takerFROM first_tx fCROSS JOIN last_day mWHERE f.cohort_date <= m.last_day - INTERVAL '30' DAY),cnt AS (-- 以“天”为活跃单位,避免同日多笔夸大SELECT u.taker, COUNT(*) AS active_daysFROM user_day uJOIN eligible e ON e.taker = u.takerGROUP BY u.taker)SELECTCOUNT(*) AS total_users,SUM(CASE WHEN active_days >= 2 THEN 1 ELSE 0 END) AS repeat_users,CAST(SUM(CASE WHEN active_days >= 2 THEN 1 ELSE 0 END) AS DOUBLE)/ NULLIF(COUNT(*), 0) AS repeat_rateFROM cnt