from near.core.fact_transfers

where STATUS = 'TRUE'

group by 1

),

tab2 as ( select TX_SIGNER, sum(deposit / power(10, 24)) as out_volume

from near.core.fact_transfers

where STATUS = 'TRUE'

group by 1

),

near_balance as ( select *, in_volume - out_volume as net_balance

from tab1 left outer join tab2 on tx_signer = tx_receiver

having net_balance > 0

,

final as ( select tx_receiver, net_balance

from near_balance

where tx_receiver not in ('wrap.near','token.sweat','app.nearcrowd.near','proximity-prime.near','binancecold3.near','meta-pool.near','aurora','nfendowment03.near','kucoinc.near')

and tx_receiver not like '%lockup%' and tx_receiver not like '%nfeco%' and tx_receiver not like '%linear%'

and tx_receiver not like '%nfendowment%' and tx_receiver not like '%stader%' and tx_receiver not like '%e-near%'

and tx_receiver not like '%binance%' and tx_receiver not like '%marketplace%'

order by 2 desc

limit 20)

,

swap as ( select date(block_timestamp) as date, trader as user, tx_hash

from near.core.ez_dex_swaps

where trader in (select tx_receiver from final ))

,

stake as (select date(block_timestamp) as date, TX_SIGNER as user, tx_hash

from near.core.dim_staking_actions

where action = 'Stake'

and TX_SIGNER in (select tx_receiver from final ))

,

unstake as (select date(block_timestamp) as date, TX_SIGNER as user, tx_hash

from near.core.dim_staking_actions

where action = 'Unstake'