with base_tb as ( SELECT tx_from as sender, date_trunc('month',block_timestamp) as date, min(date_trunc('month',block_timestamp)) over (partition by sender) as earliest_date, datediff('month',min(date_trunc('month',block_timestamp)) over (partition by sender),date_trunc('month',block_timestamp)) as difference from osmosis.core.fact_transactions where block_timestamp >= '2023-01-01' and TX_SUCCEEDED = 'TRUE' ), count_new_users as( SELECT earliest_date, count(distinct sender) as new_users from base_tb where datediff('month',earliest_date, current_date())<=15 group by 1 ), count_returning_users as ( select earliest_date , difference , count(distinct sender) as existing_users from base_tb where difference != 0 group by 1,2 ), long_retention_table as( select count_new_users.earliest_date , difference , new_users , existing_users , round(existing_users / new_users,2) as retention_pct from count_new_users left join count_returning_users on count_new_users.earliest_date = count_returning_users.earliest_date ), pivot as( select earliest_date ,case when earliest_date>=current_date - interval '15 months' and earliest_date<=current_date - interval '14 months' then 'Cohort 1' when earliest_date>=current_date - interval '14 months' and earliest_date<=current_date - interval '13 months' then 'Cohort 2' when earliest_date>=current_date - interval '13 months' and earliest_date<=current_date - interval '12 months' then 'Cohort 3' when earliest_date>=current_date - interval '12 months' and earliest_date<=current_date - interval '11 months' then 'Cohort 4' when earliest_date>=current_date - interval '11 months' and earliest_date<=current_date - interval '10 months' then 'Cohort 5' when earliest_date>=current_date - interval '10 months' and earliest_date<=current_date - interval '9 months' then 'Cohort 6' when earliest_date>=current_date - interval '9 months' and earliest_date<=current_date - interval '8 months' then 'Cohort 7' when earliest_date>=current_date - interval '8 months' and earliest_date<=current_date - interval '7 months' then 'Cohort 8' when earliest_date>=current_date - interval '7 months' and earliest_date<=current_date - interval '6 months' then 'Cohort 9' when earliest_date>=current_date - interval '6 months' and earliest_date<=current_date - interval '5 months' then 'Cohort 10' when earliest_date>=current_date - interval '5 months' and earliest_date<=current_date - interval '4 months' then 'Cohort 11' when earliest_date>=current_date - interval '4 months' and earliest_date<=current_date - interval '3 months' then 'Cohort 12' when earliest_date>=current_date - interval '3 months' and earliest_date<=current_date - interval '2 months' then 'Cohort 13' when earliest_date>=current_date - interval '2 months' and earliest_date<=current_date - interval '1 months' then 'Cohort 14' when earliest_date>=current_date - interval '1 months' then 'Cohort 15' end as cohort_id , new_users , case when difference = 1 then retention_pct end as one_month_later , case when difference = 2 then retention_pct else null end as two_month_later , case when difference = 3 then retention_pct else null end as three_month_later , case when difference = 4 then retention_pct else null end as four_month_later , case when difference = 5 then retention_pct else null end as five_month_later , case when difference = 6 then retention_pct else null end as six_month_later , case when difference = 7 then retention_pct else null end as seven_month_later , case when difference = 8 then retention_pct else null end as eight_month_later , case when difference = 9 then retention_pct else null end as nine_month_later , case when difference = 10 then retention_pct else null end as ten_month_later , case when difference = 11 then retention_pct else null end as eleven_month_later , case when difference = 12 then retention_pct else null end as twelve_month_later , case when difference = 13 then retention_pct else null end as thirteen_month_later , case when difference = 14 then retention_pct else null end as fourteen_month_later , case when difference = 15 then retention_pct else null end as fifteen_month_later from long_retention_table ) , aggregate as( select cohort_id , TO_VARCHAR(earliest_date, 'yyyy-MM') as "First interaction" , to_varchar(new_users, '999,999,999,999') as "New users" , concat(max(one_month_later) * 100 ,'%') as "1 month later" , concat(max(two_month_later) * 100 ,'%') as "2 month later" , concat(max(three_month_later) * 100 ,'%') as "3 month later" , concat(max(four_month_later) * 100 ,'%') as "4 month later" , concat(max(five_month_later) * 100 ,'%') as "5 month later" , concat(max(six_month_later) * 100 ,'%') as "6 month later" , concat(max(seven_month_later) * 100 ,'%') as "7 month later" , concat(max(eight_month_later) * 100 ,'%') as "8 month later" , concat(max(nine_month_later) * 100 ,'%') as "9 month later" , concat(max(ten_month_later) * 100 ,'%') as "10 month later" , concat(max(eleven_month_later) * 100 ,'%') as "11 month later" , concat(max(twelve_month_later) * 100 ,'%') as "12 month later" , concat(max(thirteen_month_later) * 100 ,'%') as "13 month later" , concat(max(fourteen_month_later) * 100 ,'%') as "14 month later" , concat(max(fifteen_month_later) * 100 ,'%') as "15 month later" from pivot group by 1,2,3 having cohort_id is not null ) select * from aggregate ORDER BY LENGTH(cohort_id), cohort_id

Osmosis comprehensive report