from avalanche.core.fact_transactions

where block_timestamp::date >= current_date - 210

and from_address not in (select address from avalanche.core.dim_labels)

group by 1)

,

active_users as ( select DISTINCT FROM_ADDRESS

from active

where total_week >= 14)

,

transaction as ( select date(a.block_timestamp) as date,CONTRACT_NAME, a.tx_hash, from_address, amount_usd

from avalanche.core.ez_token_transfers a join avalanche.core.ez_decoded_event_logs d on a.tx_hash = d.tx_hash

where a.block_timestamp >= current_date - 210

and from_address not in (select address from avalanche.core.dim_labels)

and a.tx_hash not in (select tx_hash from avalanche.core.ez_token_transfers

where to_address = '0x0000000000000000000000000000000000000000'

or from_address = '0x0000000000000000000000000000000000000000')

and symbol not in ('Mu Inu'))

,

final as ( select trunc(date,'week') as weekly, contract_name, a.from_address, count(DISTINCT(tx_hash)) as total_tx, sum(amount_usd) as volume

from transaction a join active_users b on a.from_address = b.from_address

group by 1,2,3)

select contract_name, count(DISTINCT(from_address)) as total_user, sum(total_tx) as total_txs, sum(volume) as total_volume,

avg(total_tx) as avg_tx, rank() over (order by total_user desc) as rank

from final

where contract_name is not null

group by 1

qualify rank <= 10