with prices as (

select recorded_at::date as pdate,

lower (symbol) as symbol,

avg (price) as price_usd

from osmosis.core.dim_prices

group by 1,2

union ALL

select

date_trunc('day', hour) as pdate,

case token_address

when lower('0x467719aD09025FcC6cF6F8311755809d45a5E5f3') then 'AXL'

when lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') then 'USDC'

when lower('0xdAC17F958D2ee523a2206206994597C13D831ec7') then 'USDT'

when lower('0x6B175474E89094C44Da98b954EedeAC495271d0F') then 'DAI'

when lower('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599') then 'WBTC'

when lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') then 'WETH'

when lower('0x27292cf0016E5dF1d8b37306B2A98588aCbD6fCA') then 'ATOM'

when lower('0x9f8F72aA9304c8B593d555F12eF6589cC3A579A2') then 'MKR'

when lower('0x085416975fe14c2a731a97ec38b9bf8135231f62') then 'UST'

when lower('0x31dab3430f3081dff3ccd80f17ad98583437b213') then 'LUNAC'

when lower('0x9ffc3bcde7b68c46a6dc34f0718009925c1867cb') then 'DOT'

end as symbol,

avg(price) as price_usd

from ethereum.core.fact_hourly_token_prices

GROUP BY 1,2

),

raw as (

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,