Query editor
WITH user_activity AS (-- 1. Sacamos la primera vez que vimos a cada usuario (Cohorte)SELECTevt_tx_from AS user_address,MIN(DATE_TRUNC('month', evt_block_time)) AS cohort_monthFROM morpho_blue_ethereum.MorphoBlue_evt_SupplyGROUP BY 1),monthly_active_users AS (-- 2. Sacamos en qué meses estuvo activo cada usuario (cualquier mes)SELECT DISTINCTevt_tx_from AS user_address,DATE_TRUNC('month', evt_block_time) AS activity_monthFROM morpho_blue_ethereum.MorphoBlue_evt_Supply),cohort_size AS (-- 3. Calculamos el tamaño inicial de cada cohorteSELECTcohort_month,COUNT(DISTINCT user_address) AS new_users_countFROM user_activityGROUP BY 1),retention_data AS (-- 4. Cruzamos usuarios con su actividad futuraSELECTu.cohort_month,mau.activity_month,COUNT(DISTINCT u.user_address) AS retained_usersFROM user_activity uJOIN monthly_active_users mau ON u.user_address = mau.user_addressWHERE mau.activity_month >= u.cohort_month -- Solo actividad futura o presenteGROUP BY 1, 2)SELECTcast(r.cohort_month as date) as cohort_month,s.new_users_count AS cohort_size,-- Mes relativo (0 = mes de registro, 1 = mes siguiente)cast(date_diff('month', r.cohort_month, r.activity_month) as int) AS month_number,r.retained_users,-- Porcentaje de retención(CAST(r.retained_users AS DOUBLE) / s.new_users_count) * 100 AS retention_rateFROM retention_data rJOIN cohort_size s ON r.cohort_month = s.cohort_monthWHERE r.cohort_month >= DATE('2024-01-01') -- Filtramos desde 2024 para ver datos recientesORDER BY 1, 3