select FROM_ADDRESS,

min (BLOCK_TIMESTAMP)::date min_date ,

from avalanche.core.fact_transactions

group by 1 having min_date >= current_date - 90

),

new_user_final as (

select count (DISTINCT FROM_ADDRESS) num_new_users,

count (tx_hash) num_txns_by_new_users

from avalanche.core.fact_transactions

where FROM_ADDRESS in (select FROM_ADDRESS from new_users)

) ,

active_users as (

select

count (tx_hash) num_transactions ,

count (DISTINCT FROM_ADDRESS) num_active_users,

sum (TX_FEE) paid_fees ,

num_transactions / num_active_users avg_transaction_per_user,

paid_fees / num_transactions avg_paid_fees_per_txn

from avalanche.core.fact_transactions

where BLOCK_TIMESTAMP::date >= current_date - 90

and BLOCK_TIMESTAMP::date < date_trunc('week',current_date )

select num_new_users ,

num_active_users ,

num_transactions ,

paid_fees,

avg_transaction_per_user,

avg_paid_fees_per_txn,

from new_user_final , active_users