Query editor
WITH user_activity AS (SELECT tx_from AS user,DATE_TRUNC('day', MIN(block_time)) AS first_day_active,DATE_TRUNC('day', MAX(block_time)) AS last_day_activeFROM dex.tradesWHERE 1=1AND blockchain = 'hyperevm'AND project = 'prjx'GROUP BY 1)SELECT first_day_active AS cohort_day,COUNT(DISTINCT user) AS new_users,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '1' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_1d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '2' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_2d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '3' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_3d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '4' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_4d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '5' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_5d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '6' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_6d,CAST(COUNT(DISTINCT CASE WHEN last_day_active >= first_day_active + INTERVAL '7' DAY THEN user END) AS DOUBLE) / COUNT(DISTINCT user) AS retention_rate_7d,AVG(DATE_DIFF('day', first_day_active, last_day_active)) AS avg_days_active,SUM(COUNT(DISTINCT user)) OVER (ORDER BY first_day_active) AS total_usersFROM user_activityGROUP BY 1ORDER BY 1