base as (

SELECT

DISTINCT

date_trunc('MONTH', date(block_timestamp)) as month_active,

from_address as address

FROM polygon.core.fact_transactions

),

user_cohort as (

SELECT

date_trunc('MONTH', min(date(block_timestamp))) as month_joined,

from_address as address

from polygon.core.fact_transactions

group by address

),

cohort_size as (

select

count(*) as total_size,

month_joined

from user_cohort

group by 2

),

retention as (

SELECT

user_cohort.month_joined,

cohort_size.total_size,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 1, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_1_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 2, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_2_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 3, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_3_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 4, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_4_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 5, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_5_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 6, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_6_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 7, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_7_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 8, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_8_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 9, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_9_retention,

SUM(CASE WHEN base.month_active = DATEADD('MONTH', 10, user_cohort.month_joined) THEN 1 ELSE 0 END) / cohort_size.total_size * 100 AS month_10_retention,