from axelar.core.fact_msg_attributes

where ATTRIBUTE_KEY = 'destinationChain'

and block_timestamp::date >= CURRENT_DATE - 180)

,

user as ( select ATTRIBUTE_VALUE as user, tx_id

from axelar.core.fact_msg_attributes

where tx_id in (select tx_id from tx)

and ATTRIBUTE_KEY = 'spender')

,

tb1 as ( select DISTINCT user ,date(block_timestamp) as date, a.tx_id, chain , a.ATTRIBUTE_VALUE

from axelar.core.fact_msg_attributes a join tx b on a.tx_id = b.tx_id

join user c on a.tx_id = c.tx_id

where MSG_TYPE = 'coin_spent'

and ATTRIBUTE_INDEX = 1

,

label as ( select date, attribute_value,tx_id, 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 token_name, user , chain

from tb1 )

,

amount as ( select date, tx_id , user,chain, token_name, 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 amount

from label)