select

date(recorded_at) as pdate, lower(symbol) as symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

group by pdate, symbol

),

raw as (

-- credit: alik110

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

count(distinct TX_ID) as tx_count,

sum(amount/pow(10,decimal) * price_usd) as tvol, initcap(Sender_Chain)

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

where TX_SUCCEEDED = 'TRUE'

and TRANSFER_TYPE = 'IBC_TRANSFER_IN'

and amount is not null

group by Sender_Chain