avg(price) as price_usd

from osmosis.core.dim_prices

group by 1

),

raw as (select count(distinct sender) as senders, count (distinct receiver) as receivers,

regexp_substr(sender,'[a-zA-Z]+|\d+') as Sender_Chain,

regexp_substr(receiver,'[a-zA-Z]+|\d+') as Receiver_Chain,

Sender_Chain|| ' To ' || Receiver_Chain as Transfer_Path,

lower(split(currency,'-')[0]) as Symbol1,

iff(Symbol1 ilike 'u%',substring(Symbol1,2,LEN(Symbol1)), Symbol1) as asset, *

from axelar.core.fact_transfers t group by 1, 2

)

select date_trunc('day', block_timestamp) as day, senders, receivers,

count(distinct TX_ID) as trxs,

sum(amount/pow(10,decimal) * price_usd) as usd_volume

from raw t join prices on date(t.block_timestamp) = pdate

where block_timestamp>= CURRENT_DATE - 120

and TX_SUCCEEDED = 'TRUE'

and TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

and amount is not null

group by 1