Query editor
-- half_life_day (线性内插,精确到“第几天”)WITH r AS (-- 直接把“步骤1”的最终 SELECT 当子查询贴进来也可以WITH user_day AS (SELECT taker, date_trunc('day', block_time) AS tx_dateFROM dex.tradesWHERE block_time >= current_timestamp - INTERVAL '90' DAYAND project='uniswap' AND blockchain='ethereum'),max_day AS (SELECT MAX(tx_date) AS last_day FROM user_day),first_tx AS (SELECT taker, MIN(tx_date) AS cohort_date, date_trunc('week', MIN(tx_date)) AS cohort_weekFROM user_day GROUP BY taker),active AS (SELECT DISTINCT taker, tx_date FROM user_day),rel AS (SELECT f.cohort_week, f.cohort_date, a.taker,date_diff('day', f.cohort_date, a.tx_date) AS dFROM first_tx f JOIN active a ON a.taker=f.takerWHERE a.tx_date >= f.cohort_date),agg AS (SELECT cohort_week, cohort_date, d, COUNT(DISTINCT taker) AS active_usersFROM rel WHERE d BETWEEN 0 AND 30GROUP BY cohort_week, cohort_date, d),cohort_size AS (SELECT cohort_week, cohort_date, COUNT(*) AS cohort_size FROM first_tx GROUP BY cohort_week, cohort_date),rates AS (SELECT a.cohort_week, a.cohort_date, a.d,CAST(a.active_users AS DOUBLE)/NULLIF(c.cohort_size,0) AS retention_rateFROM agg a JOIN cohort_size cON a.cohort_week=c.cohort_week AND a.cohort_date=c.cohort_date)SELECT r.*FROM rates rCROSS JOIN max_day mWHERE r.cohort_date + (r.d * INTERVAL '1' DAY) <= m.last_day),above AS (SELECT cohort_week, cohort_date,MAX(d) AS d0FROM rWHERE retention_rate >= 0.5GROUP BY cohort_week, cohort_date),below AS (SELECT cohort_week, cohort_date,MIN(d) AS d1FROM rWHERE retention_rate < 0.5GROUP BY cohort_week, cohort_date),p AS (SELECT a.cohort_week, a.cohort_date, a.d0,r0.retention_rate AS r0,b.d1,r1.retention_rate AS r1FROM above aJOIN below bON a.cohort_week=b.cohort_week AND a.cohort_date=b.cohort_date