from near.core.fact_actions_events

where action_name = 'DeployContract'

and tx_hash in (select tx_hash from near.core.fact_actions_events_function_call

where method_name = 'new'

and block_timestamp >= '2022-11-01')

and block_timestamp >= '2022-11-01'

,

final as ( select tx_receiver

from contract a join near.core.fact_transactions b on a.tx_hash = b.tx_hash

select a.tx_receiver, avg(deposit/pow(10,24)) as avg_near,

rank() over (order by avg_near desc ) as rank

from near.core.fact_transfers a join final b on a.tx_receiver = b.tx_receiver

where block_timestamp >= '2022-11-01'

and a.tx_receiver not in ('cgran01.near','nfendowment03.near','linear.sputnik-dao.near'

,'lockup.near','inc-finops.linkdropv1.near')

group by 1

qualify rank <= 5