Query editor
-- overall_retention_0_4_censor_vs_uncensor_fixed_window-- 窗口:UTC [2025-08-13, 2025-11-12)WITH user_day AS (SELECTtaker,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),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_dateFROM user_day),rel AS (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),agg AS (SELECTcohort_week,cohort_date,d_week,COUNT(DISTINCT a.taker) AS active_usersFROM rel aWHERE d_week BETWEEN 0 AND 4GROUP BY cohort_week, cohort_date, d_week),cohort_size AS (SELECTcohort_week,cohort_date,COUNT(*) AS cohort_sizeFROM first_txGROUP BY cohort_week, cohort_date),rates AS (SELECTx.cohort_week,x.cohort_date,x.d_week,