select

date_trunc('day', recorded_at) as date,

lower(symbol) as symbol,

avg(price) as asset_price

from osmosis.core.dim_prices

group by 1, 2

),

base as (

select

block_timestamp,

tx_id,

transfer_type,

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

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

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

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

initcap (sender_chain)|| ' To ' || initcap (receiver_chain) as transfer_path,

amount/pow(10,decimal) * asset_price as volume

from axelar.core.fact_transfers t left join price p on block_timestamp::date = p.date

where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT') and asset = symbol and tx_succeeded = 'TRUE'-- and amount is not null and asset_price is not null

union all

select

block_timestamp,

tx_id,

transfer_type,

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

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

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

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

initcap (sender_chain)|| ' To ' || initcap (receiver_chain) as transfer_path,

amount/pow(10,decimal) as volume

from axelar.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT') and tx_succeeded = 'TRUE' and currency in ('cusdc','usdt','uusd','uusdc','uusdt','dai')