select

date_trunc('day',recorded_at) as date1,

lower (symbol) as symbol1,

avg (price) as USD_Price

from osmosis.core.dim_prices

group by 1,2),

Tab2 as (

select

BLOCK_TIMESTAMP,

tx_id,

sender,

RECEIVER,

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 Symbol2,

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

from axelar.core.fact_transfers

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

AND not CURRENCY ilike any ('factory%', 'gravity%')

AND amount is not NULL

AND currency is not NULL

)

select

BLOCK_TIMESTAMP:: date as day,

count (distinct tx_id) as Transfers_Count,

sum (Volume*USD_Price) as USD_Volume,

avg (Volume*USD_Price) as Average_USD_Volume,

median (Volume*USD_Price) as Median_USD_Volume,

min (Volume*USD_Price) as Min_USD_Volume,

max (Volume*USD_Price) as Max_USD_Volume,