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

date_trunc('day',block_timestamp) as date,

case

when date < '2022-11-07' then 'Before FTX crisis'

when date >= '2022-11-07' then 'After FTX crisis'

end as time_gp,

count (distinct tx_id) as no_trabsfer,

count (distinct sender) as no_senders,