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 *,

(case

when symbol_M ilike 'dai' then adj_amount

when symbol_M ilike 'usdt' then adj_amount

when symbol_M ilike 'usdc' then adj_amount

when symbol_M ilike 'usd' then adj_amount

when symbol_M ilike 'cusdc' then adj_amount

when symbol_M ilike 'u%' then adj_amount * prices.usd_price

else adj_amount * prices.usd_price

end) as usd_volume

from base