Query editor
WITH weekly_traders AS (SELECT DISTINCTDATE_TRUNC('week', block_time) AS week,tx_fromFROM dex.tradesWHERE blockchain = 'mantle'AND block_time >= NOW() - INTERVAL '90' day),retention AS (SELECTw1.week AS week,COUNT(DISTINCT w1.tx_from) AS traders_last_week,COUNT(DISTINCT w2.tx_from) AS retained_tradersFROM weekly_traders w1LEFT JOIN weekly_traders w2ON w1.tx_from = w2.tx_fromAND w2.week = w1.week + INTERVAL '7' dayGROUP BY w1.week)SELECTweek,traders_last_week,retained_traders,(retained_traders * 100.0 / NULLIF(traders_last_week, 0)) AS retention_pctFROM retentionORDER BY week;