with pc as (

select

recorded_at::date as days,

lower(symbol) as symbol,

avg(price) as token_price

from

osmosis.core.dim_prices

group by 1,2

),

base1 as (

select

regexp_substr(sender,'[a-zA-Z]+|\d+') as from_chain, -- thanks to cryptoicicle for this cte

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

concat(from_chain,' to ',to_chain) as chain_pairs,

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

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

t.*

from axelar.core.fact_transfers t

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

,

base as (

select b.* , token_price , token_price * AMOUNT / pow(10,DECIMAL) as usd_amount

from base1 b , pc

where

pc.days = b.BLOCK_TIMESTAMP::date and pc.symbol = b.token_name

,fin as (select * from base where to_chain = 'osmo')