from flow.core.fact_transactions

where block_timestamp::date >= '2022-07-11' and block_timestamp::date <= '2022-07-18'

group by proposer)

,

t2 as ( select min(block_timestamp) as date_ , proposer

from flow.core.fact_transactions

where proposer in ( select proposer from t1)

and block_timestamp::date > '2022-07-18'

group by proposer)

select 'Not Retention ' as state , count(DISTINCT proposer) as unique_users

from t1

where proposer not in ( select proposer from t2)

group by 1

UNION

select 'Retention' as state , count(DISTINCT proposer) as unique_users

from t2

group by 1