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(chain_from,' to ',chain_to) 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 = 'IBC_TRANSFER_IN' and currency in ('cusdc','usdt','uusd','uusdc','uusdt','dai')

,

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

select

BLOCK_TIMESTAMP::date as date ,

from_chain,

token_name,