SELECT

a.BLOCK_TIMESTAMP,

TRY_CAST(

REGEXP_SUBSTR(a.ATTRIBUTE_VALUE, $ $ ^ \ d * $$) AS bigint

) / 1e6 AS amount,

b.ATTRIBUTE_VALUE as wallet,

a.tx_id,

a.MSG_TYPE as action,

c.ATTRIBUTE_VALUE as source_validator,

d.ATTRIBUTE_VALUE as destination_validator

from

cosmos.core.fact_msg_attributes a

join cosmos.core.fact_msg_attributes b on a.tx_id = b.tx_id

and b.MSG_TYPE = 'message'

and b.ATTRIBUTE_KEY = 'sender'

and a.MSG_GROUP = b.MSG_GROUP

and b.ATTRIBUTE_INDEX = 1

left join cosmos.core.fact_msg_attributes c on a.tx_id = c.tx_id

and c.MSG_TYPE = 'redelegate'

and c.ATTRIBUTE_KEY = 'source_validator'

left join cosmos.core.fact_msg_attributes d on a.tx_id = d.tx_id

and d.MSG_TYPE = 'redelegate'

and d.ATTRIBUTE_KEY = 'destination_validator'

where

1 = 1

and a.MSG_TYPE in ('delegate', 'redelegate', 'unbond') -- and a.tx_id = 'F99A481940C8C79B8C7EA975D8CC7A474B9267B39E76FDD44205CE733B280767' -- sample tx

and a.ATTRIBUTE_KEY = 'amount'

and a.ATTRIBUTE_VALUE like '%uatom'

),

Data as (

SELECT

so.LABEL as source_validator_name,

des.LABEL as destination_validator_name,

*

from