select TX_ID,

regexp_substr(ATTRIBUTE_VALUE,'[[:digit:]]*\.?[[:digit:]]+')/pow(10,6) as fee,

regexp_substr(ATTRIBUTE_VALUE,'[a-zA-Z]+|\d+') as currency_fee

from axelar.core.fact_msg_attributes

WHERE ATTRIBUTE_KEY = 'fee' and TX_SUCCEEDED = 'TRUE'),

prices AS

(

SELECT date_trunc('day', RECORDED_AT) as date_price, symbol, avg(price) as price

FROM osmosis.core.dim_prices GROUP BY 1,2

),

df1 as (select date_trunc('day', BLOCK_TIMESTAMP) as date,

initcap(regexp_substr(SENDER,'[a-zA-Z]+|\d+')) as origin_ecosystem,

initcap(regexp_substr(receiver,'[a-zA-Z]+|\d+')) as destination_ecosystem,

concat ('origin_ecosystem','destination_ecosystem') as from_to,

a.tx_id, sender, receiver,

TRANSFER_TYPE, AMOUNT/pow(10,decimal) as amount, CASE WHEN currency = 'uaxl' then 'AXL'

WHEN currency = 'uusd' then 'USDC'

WHEN currency = '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then 'UST'

WHEN currency = 'usdt' then 'USDT'

WHEN currency = 'uusdt' then 'USDT'

else UPPER(split(currency,'-')[0])end AS CURRENCY,

fee

from axelar.core.fact_transfers a join fees b on a.tx_id = b.tx_id

WHERE TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT') and TX_SUCCEEDED = 'TRUE' and date > '2022-10-01'),

df as (

select date, origin_ecosystem, destination_ecosystem, sender, from_to, tx_id, TRANSFER_TYPE, currency, amount*price as price_usd, fee

from df1 join prices on df1.date = prices.date_price and df1.CURRENCY = prices.symbol

UNION

select date, origin_ecosystem, destination_ecosystem, sender, from_to, tx_id, TRANSFER_TYPE, currency,

case when currency = 'USDT' then amount

when currency = 'USDC' then amount end as price_usd, fee