ins as (

SELECT

trunc(block_timestamp,'day') as date,

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,2

),

outs as (

SELECT

trunc(block_timestamp,'day') as date,

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,2

),

holders as (

SELECT

ifnull(ins.date,outs.date) as date,

ifnull(ins.wallet,outs.wallet) as address,

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

from ins

full outer join outs on ins.wallet=outs.wallet and ins.date=outs.date

where amount_in is not null and amount_in>0

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

select

date,

count(distinct ADDRESS) as wallets,

sum(wallets) over (order by date) as wallet,

sum(BALANCE) as amounts,

sum(amounts) over (order by date) as amount,

avg(amounts) over (order by date) as avg_amount