with pricet as (select recorded_at::date as day,

lower (symbol) as symbol,

avg (price) as USDPrice

from osmosis.core.dim_prices

group by 1,2

union ALL

select recorded_at::date as day,

'axl' as symbol2,

avg (price) as USDPrice

from osmosis.core.dim_prices

where symbol ilike 'axl'

group by 1,2),

Table1 as (select *,

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 Symbol

from axelar.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')

and TX_SUCCEEDED = 'TRUE'),

Table2 as (select t1.*,

(amount*USDPrice / POW (10,decimal)) as USDVolume

from Table1 t1 left join pricet t2 on t1.block_timestamp::date = t2.day and t1.symbol = t2.symbol

union ALL

select *,

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,

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

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

(amount / POW (10,decimal)) as USDVolume

from axelar.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')

and currency in ('cusdc','usdt','uusd','uusdc','uusdt','dai')