Query editor
WITH Users AS (SELECT"from" AS user,MIN(date_trunc('week', block_time)) AS week_cohort,MIN(date_trunc('day', block_time)) AS first_week,COUNT(DISTINCT(block_time)) AS distinct_days_active,MAX(date_trunc('day', block_time)) AS last_week_activeFROM ethereum.transactionsWHERE hash in (SELECT Hash FROM query_5494012) AND successGROUP BY 1HAVING MIN(date_trunc('week', block_time)) >= date_trunc('week', CURRENT_TIMESTAMP - interval '12' month))SELECTweek_cohort,COUNT(DISTINCT user) as total_users,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '7' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_1,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '14' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_2,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '21' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_3,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '28' day THEN user END) AS DOUBLE)/ COUNT(DISTINCT user) * 1.00 AS retention_rate_4,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '35' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_5,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '42' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_6,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '49' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_7,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '56' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_8,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '63' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_9,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '70' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_10,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '77' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_11,CAST(COUNT(DISTINCT CASE WHEN last_week_active >= first_week + interval '84' day THEN user END) AS DOUBLE) / COUNT(DISTINCT user) * 1.00 AS retention_rate_12FROM UsersWHERE week_cohort >= date('2024-12-18') --SoniccccGROUP BY week_cohortORDER BY week_cohort