with new_users as(

select proposer,

min(block_timestamp) reg_date

from flow.core.fact_transactions

group by proposer

),

new_users_id as(

select proposer from

(select

date_trunc('day', fact_tx.block_timestamp) day,

fact_tx.proposer,

date_trunc('day', new_users.reg_date) reg_day,

case

when day = reg_day then 'new' else 'old'

end is_new

from flow.core.fact_transactions fact_tx

left join new_users

on fact_tx.proposer = new_users.proposer

where day >= current_date - 60

order by day

) x

where x.is_new = 'new'

),

tx_per_user as(

select x.proposer,

count(x.tx_id) total_tx

from

(select distinct new_users_id.proposer,

fact_tx.tx_id

from new_users_id

left join flow.core.fact_transactions fact_tx

on new_users_id.proposer = fact_tx.proposer

) x

group by x.proposer