recorded_at::date as day,

lower (symbol) as symbol,

avg (price) as usd_price

from osmosis.core.dim_prices

group by 1,2

),

tb2 as (

select *,

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

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

CONCAT(source_chain,' -> ',Destination_chain) as Path_transfer,

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

iff(symbol ilike 'u%', substring(symbol, 2, LEN(symbol)), symbol) as tokens

from axelar.core.fact_transfers

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

and TX_SUCCEEDED = 'TRUE'),

tb3 as (select

*,

case

when currency in ('cusdc','usdt','uusd','uusdc','uusdt','dai') then (amount/POW (10,decimal))*1

else (amount/POW (10,decimal))*usd_price

end as volume_USD

from tb2 s

left join tb1 b on s.block_timestamp::date = b.day and s.tokens = b.symbol

select

Path_transfer,

count (distinct tx_id) as no_trabsfer,

count (distinct sender) as no_senders,

count(distinct RECEIVER) as no_receivers,

sum(Volume_usd) as transfer_usd,

avg(Volume_usd) as avg_transfer_usd