Query editor
-- weekly_cohort_retention_rate_fixedWITH user_day AS (SELECT taker, 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'),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 (SELECTf.cohort_week,f.cohort_date,f.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 (SELECT cohort_week, d_week, COUNT(DISTINCT taker) AS active_usersFROM relWHERE d_week BETWEEN 0 AND 4GROUP BY cohort_week, d_week),cohort_size AS (SELECT cohort_week, COUNT(*) AS cohort_sizeFROM first_txGROUP BY cohort_week)SELECTagg.cohort_week,agg.d_week,agg.active_users,cs.cohort_size,CAST(agg.active_users AS DOUBLE)/NULLIF(cs.cohort_size,0) AS retention_rateFROM agg aggJOIN cohort_size cs ON agg.cohort_week = cs.cohort_weekORDER BY agg.cohort_week, agg.d_week