select

recorded_at::date as day,

lower(symbol) as asset,

avg(price) as usd_average_price

from osmosis.core.dim_prices

group by 1, 2

),

source as (

select

block_timestamp::date as day,

tx_id,

sender,

receiver,

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

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

transfer_type,

amount,

decimal

from axelar.core.fact_transfers

where block_timestamp::date > CURRENT_DATE - 30

and transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

and TX_SUCCEEDED = 'TRUE'),

table1 as (

select

s.*,

((amount * usd_average_price)/pow(10,decimal)) as usd_amount

from source s left join prices p on (s.day = p.day and p.asset = s.token)

union all

select

block_timestamp::date as day,

tx_id,