ins as (

SELECT

receiver as wallet,

sum(amount/pow(10,decimal)) as amount_in

from axelar.core.fact_transfers

where currency ='uaxl' and transfer_type in ('IBC_TRANSFER_IN','AXELAR')

group by 1

),

outs as (

SELECT

sender as wallet,

sum(amount/pow(10,decimal)) as amount_out

from axelar.core.fact_transfers

where currency ='uaxl' and transfer_type in ('IBC_TRANSFER_OUT','AXELAR')

group by 1

),

holders as (

SELECT

ins.wallet as address,

ifnull(amount_in,0)-ifnull(amount_out,0) as balance

from ins

full outer join outs on ins.wallet=outs.wallet where amount_in is not null and amount_in>0

and amount_in<1e9 and amount_out<1e9 and amount_out>0

)

select

count(distinct ADDRESS) as wallet,

sum(BALANCE) as amount,

avg(BALANCE) as avg_amount

from holders