base as (

SELECT

DISTINCT

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

t.signers[0] as address

FROM

solana.core.fact_transactions t

where

date(t.block_timestamp) between '{{start_date}}' and '{{end_date}}'

),

user_cohort as (

SELECT

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

t.signers[0] as address

from

solana.core.fact_transactions t

group by 2

),

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 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 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 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 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 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 AS month_6_retention,