prices as (

select recorded_hour::date as day,

symbol as token,

avg (open) as price_usd

from crosschain.core.fact_hourly_prices

left join crosschain.core.dim_asset_metadata

group by 1, 2

),

tbl as (

select a.block_timestamp::date as day,

tx_id,

a.attribute_value as origin_chain,

c.attribute_value as origin_user,

e.attribute_value as destination_chain,

b.attribute_value as destination_user,

case when d.attribute_value like '%uaxl' then 'AXL'

when d.attribute_value like '%uusdc' then 'USDC'

when d.attribute_value like '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F' then 'UST'

when d.attribute_value like '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E' then 'ATOM'

when d.attribute_value like '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8' then 'LUNA'

when d.attribute_value like '%weth-wei' then 'ETH'

when d.attribute_value like '%wbtc-satoshi' then 'WBTC'

else 'Other' end as token,

case token

when 'AXL' then cast(replace(d.attribute_value, 'uaxl', '') as int) / 1e6

when 'USDC' then cast(replace(d.attribute_value, 'uusdc', '') as int) / 1e6

when 'UST' then cast(replace(d.attribute_value, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as int) / 1e6

when 'ATOM' then cast(replace(d.attribute_value, 'ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E', '') as int) / 1e6

when 'LUNA' then cast(replace(d.attribute_value, 'ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8', '') as int) / 1e6

when 'ETH' then cast(replace(d.attribute_value, 'weth-wei', '') as int) / 1e18

when 'WBTC' then cast(replace(d.attribute_value, 'wbtc-satoshi', '') as int) / 1e9

end as amount

from axelar.core.fact_msg_attributes a