select

date_trunc('week', block_timestamp) as time,

proposer

from flow.core.fact_transactions

where 1=1

group by 1, 2

),

user_transactions_1 as (

select

date_trunc('week', block_timestamp) + interval'1 week' as time_1,

proposer as proposer_1

from flow.core.fact_transactions

where 1=1

group by 1, 2

select

time,

count_if(time_1 is not null) as number_of_retained_users,

count(*) as number_of_users,

number_of_retained_users * 100 / number_of_users as retention_rate

from user_transactions

left join user_transactions_1 on (time = time_1 and proposer = proposer_1)

group by 1

order by 1 desc