with near_transferin as ( select tx_receiver as users , sum(deposit/pow(10,24)) as in_amount from near.core.fact_transfers group by users)

, near_transferout as (select tx_signer as users, sum (deposit/pow(10,24)) as out_amount from near.core.fact_transfers group by users )

, near_transfer_banalnce as ( select near_transferin.users , sum(in_amount-out_amount) as near_balance

from near_transferin join near_transferout on near_transferin.users=near_transferout.users

group by near_transferin.users

having near_balance > 0)

,nearswap_from as ( select trader as users, sum(amount_in) as in_amount from near.core.ez_dex_swaps where token_in in ('stNEAR','NEAR','wNEAR') group by users)

,nearswap_to as ( select trader as users,sum (amount_out) as out_amount from near.core.ez_dex_swaps where token_out in ('stNEAR','NEAR','wNEAR') group by users)

,near_swap_balance as ( select nearswap_from.users , sum(in_amount-out_amount) as near_balance

from nearswap_from inner join nearswap_to on nearswap_to.users=nearswap_from.users

group by nearswap_from.users

having near_balance > 0)

, near_transfer_swaps as (select users , near_balance from near_transfer_banalnce union select users , near_balance from near_swap_balance)

, top20holders as (select users , sum(near_balance) as final_balance from near_transfer_swaps group by users

order by final_balance desc

limit 20)

select count(*) , sum(deposit/pow(10,24)) , sum(TRANSACTION_FEE/pow(10,24)) , tx_receiver from near.core.fact_transfers

where tx_receiver in (select users from top20holders)

group by tx_receiver