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(CAST(SPLIT(ATTRIBUTE_VALUE, 'usei')[0] AS DECIMAL) / 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 validator

),

undelegate AS (

SELECT

tx_id,

ATTRIBUTE_VALUE AS validator

FROM sei.core.fact_msg_attributes

WHERE ATTRIBUTE_KEY = 'validator' AND msg_type = 'unbond'

),

undelegate_amount AS (

SELECT

validator,

SUM(CAST(SPLIT(ATTRIBUTE_VALUE, 'usei')[0] AS DECIMAL) / 1e6) AS unstake_amount

FROM sei.core.fact_msg_attributes a

INNER JOIN undelegate b USING(tx_id)

WHERE msg_type = 'unbond' AND ATTRIBUTE_KEY = 'amount'

GROUP BY validator

),

final AS (

SELECT

a.validator,

COALESCE(stake_amount, 0) - COALESCE(unstake_amount, 0) AS net,