Query editor
WITH user_months AS (SELECT DISTINCT"from" AS user_address,DATE_TRUNC('month', block_date) AS activity_monthFROM ink.transactionsWHERE success = TRUEAND "from" IS NOT NULL),first_activity AS (SELECTuser_address,MIN(activity_month) AS cohort_monthFROM user_monthsGROUP BY 1),cohort_activity AS (SELECTf.cohort_month,u.activity_month,COUNT(DISTINCT u.user_address) AS active_usersFROM first_activity fJOIN user_months uON f.user_address = u.user_addressGROUP BY 1, 2),cohort_size AS (SELECTcohort_month,COUNT(DISTINCT user_address) AS cohort_usersFROM first_activityGROUP BY 1)SELECTca.cohort_month,ca.activity_month,DATE_DIFF('month',ca.cohort_month,ca.activity_month) AS month_number,ca.active_users,cs.cohort_users,ROUND(100.0 * ca.active_users/ NULLIF(cs.cohort_users, 0),2) AS retention_rate_pctFROM cohort_activity caJOIN cohort_size csON ca.cohort_month = cs.cohort_monthORDER BY