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('{{Period}}', RECORDED_AT) as date_price, symbol, avg(price) as price

FROM osmosis.core.dim_prices GROUP BY 1,2

),

df1 as (select date_trunc('{{Period}}', 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,

UPPER(split(currency,'-')[0]) 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

from df1 where currency in ('USDT', 'USDC')

select DATE, origin_ecosystem,