attribute_value as to_chain

FROM axelar.core.fact_msg_attributes

WHERE attribute_key = 'destinationChain'

),

tab2 as (select date_trunc('day', block_timestamp) as date,

CASE WHEN attribute_value LIKE '%uaxl%' THEN 'AXL'

WHEN attribute_value LIKE '%uusdc%' THEN 'USDC'

WHEN attribute_value LIKE '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' THEN 'UST'

WHEN attribute_value LIKE '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' THEN 'ATOM'

WHEN attribute_value LIKE '%weth-wei%' THEN 'ETH'

WHEN attribute_value LIKE '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' THEN 'LUNA'

WHEN attribute_value LIKE '%wbtc-satoshi%' THEN 'WBTC'

ELSE 'Other' END as axl_symbol,

to_chain,

count(DISTINCT tx_id) as events,

sum(CASE WHEN attribute_value LIKE '%uaxl%' THEN cast(REPLACE(attribute_value, 'uaxl', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%uusdc%' THEN cast(REPLACE(attribute_value, 'uusdc', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' THEN cast(REPLACE(attribute_value, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' THEN cast(REPLACE(attribute_value, 'ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%weth-wei%' THEN cast(REPLACE(attribute_value, 'weth-wei', '') as INT) / power(10, 18)

WHEN attribute_value LIKE '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' THEN cast(REPLACE(attribute_value, 'ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8', '') as INT) / power(10, 6)

WHEN attribute_value LIKE '%wbtc-satoshi%' THEN cast(REPLACE(attribute_value, 'wbtc-satoshi', '') as INT) / power(10, 9) END

) as toke_amt

from axelar.core.fact_msg_attributes

LEFT outer JOIN tab1 ON tx = tx_id

WHERE msg_type LIKE 'coin_spent'

AND attribute_index = 1

AND not to_chain is NULL

GROUP BY 1,2,3

),

tab3 as (SELECT date_trunc('day', RECORDED_HOUR) as week2,

symbol,

median(open) as price

FROM crosschain.core.fact_hourly_prices

LEFT outer JOIN crosschain.core.dim_asset_metadata ON crosschain.core.fact_hourly_prices.id LIKE crosschain.core.dim_asset_metadata.id