SELECT date_trunc('month', BLOCK_TIMESTAMP) as cohort_month, TX_SIGNER as user

from near.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_SIGNER) 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 near.core.fact_transactions

join users u on user = TX_SIGNER 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",

"7" as "7 Month", "8" as "8 Month",

"9" as "9 Month", "10" as "10 Month",

"11" as "11 Month", "12" as "12 Month"

from agg_data

PIVOT