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,