select block_timestamp::date as day,

blockchain,

transfer_type,

chain_id,

tx_id,

sender,

receiver,

amount / pow(10, decimal) as adj_amount,

currency,

split_part(currency, '-', 1) as temp,

(case

when temp ilike 'u%' then regexp_replace(temp, 'u', '', 1, 1)

else temp

end) as symbol_M

from axelar.core.fact_transfers

where transfer_type ilike 'IBC_TRANSFER%'

and tx_succeeded = 'TRUE'

),

prices as (

select hour::date as day, lower(p.symbol) as symbol, avg(price) as usd_price

from ethereum.core.fact_hourly_token_prices p

group by day, symbol

),

transfers_with_prices as (

select base.day,

base.blockchain,

base.transfer_type,

base.chain_id,

base.tx_id,

base.sender,

base.receiver,

base.adj_amount,

base.currency,

symbol_M,

(case