select

date_trunc('day',recorded_at) as p_date,

lower(symbol) as symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

group by 1,2

,bridge as (

SELECT

BLOCK_TIMESTAMP,

tx_id,

sender,

CASE

WHEN sender Ilike 'terra%' then 'terra'

WHEN sender Ilike 'kujira%' THEN 'kujira'

WHEN sender Ilike 'osmo%' then 'osmo'

WHEN sender Ilike 'juno%' then 'juno'

WHEN sender Ilike 'secret%' then 'secret'

WHEN sender Ilike 'cosmos%' then 'cosmos'

WHEN sender Ilike 'stars%' then 'stargaze'

WHEN sender Ilike 'i%' then 'ki'

WHEN sender Ilike 'cre%' then 'Crescent'

WHEN sender Ilike 'emoney%' then 'e-money'

END as chain,

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

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

(amount/pow(10,decimal)) as adjusted_amt

from axelar.core.fact_transfers

where transfer_type = 'IBC_TRANSFER_IN'

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

AND amount is not NULL

AND currency is not NULL

AND chain is not NULL

)