t1 as (

select

block_timestamp,

case when currency='ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' then 'axlUSDC'

when currency='ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' then 'axlUSDT'

end as stablecoin,

case when stablecoin in ('axlUSDC','axlUSDT') then 6 end as decimal,

amount,

sender,

receiver,

transfer_type,

tx_id

from terra.core.ez_transfers x

where currency in ('ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4',

'ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF')

and message_type in ('/ibc.applications.transfer.v1.MsgTransfer','/cosmos.bank.v1beta1.MsgMultiSend','/cosmos.bank.v1beta1.MsgSend')

),

t2 as (

SELECT

date_Trunc('{{Frequency}}',block_timestamp) as timespan,

stablecoin,

count(distinct tx_id) as transfers_in,

count(distinct sender) as users_depositing,

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

avg(amount/pow(10,decimal)) as avg_amount_transferred_in,

sum(transfers_in) over (partition by stablecoin order by timespan) as cum_transfers_in,

sum(amount_transferred_in) over (partition by stablecoin order by timespan) as cum_amount_transferred_in

from t1 where transfer_type='IBC_Transfer_In'

group by 1,2

),

t3 as (

SELECT

date_Trunc('{{Frequency}}',block_timestamp) as timespan,

stablecoin,

count(distinct tx_id) as transfers_out,