with delegate as (

SELECT

tx_id ,

ATTRIBUTE_VALUE as validator

FROM sei.core.fact_msg_attributes

WHERE ATTRIBUTE_KEY = 'validator' AND msg_type = 'delegate'

,

delegate_amount as (

SELECT

validator,

sum((split(ATTRIBUTE_VALUE, 'usei')[0])/1e6) as stake_amount

FROM sei.core.fact_msg_attributes a

INNER JOIN delegate b using(tx_id)

WHERE msg_type = 'delegate' AND ATTRIBUTE_KEY = 'amount'

GROUP BY 1

,

redelegate_to as (

SELECT

tx_id,

ATTRIBUTE_VALUE as validator

FROM sei.core.fact_msg_attributes

WHERE ATTRIBUTE_KEY = 'destination_validator' AND msg_type = 'redelegate'

,

redelegate_to_amount as (

SELECT

validator,

sum((split(ATTRIBUTE_VALUE, 'usei')[0])/1e6) as redelegate_amount_to

FROM sei.core.fact_msg_attributes

INNER JOIN redelegate_to b using(tx_id)

WHERE msg_type = 'redelegate' AND ATTRIBUTE_KEY = 'amount'

GROUP BY 1