SELECT

date_trunc('month', BLOCK_TIMESTAMP) as cohort_month,

TX_FROM as user

from axelar.core.fact_transactions

-- where cohort_month >= '2022-05-01'

GROUP BY cohort_month, user

),

total_users as (

SELECT

cohort_month as tcohort_month,

COUNT(DISTINCT user) as total_users_count

from users

GROUP BY tcohort_month

),

agg_data as (

cohort_month,

date_trunc('month', BLOCK_TIMESTAMP) as date,

COUNT(DISTINCT TX_FROM) as retained_users,

avg(total_users_count) as total_users_count2,

retained_users/total_users_count2 * 100 as perc,

row_number() over (partition by cohort_month order by date ASC) as ord

from axelar.core.fact_transactions

join users u on user = TX_FROM and date_trunc('month', BLOCK_TIMESTAMP) > cohort_month

join total_users tu on tcohort_month = cohort_month

-- where date >= to_date('2022-05-01')

GROUP by cohort_month, date

ORDER BY (cohort_month, date) ASC

),

all_data as (

cohort_month, total_users_count2,

"1" as "1 Month", "2" as "2 Month",

"3" as "3 Month", "4" as "4 Month",

"5" as "5 Month", "6" as "6 Month",