-- Step 1: Filter relevant rows early to reduce dataset size

SELECT *

FROM sei.core.fact_msg_attributes

WHERE msg_type = 'unbond'

AND ATTRIBUTE_KEY IN ('validator', 'amount')

),

tx_validators AS (

-- Step 2: Get distinct tx_id and validator mapping

SELECT DISTINCT

tx_id,

ATTRIBUTE_VALUE AS validator

FROM filtered_data

WHERE ATTRIBUTE_KEY = 'validator'

),

validator_amounts AS (

-- Step 3: Get amounts, converting from macro SEI to SEI

SELECT

tx_id,

CAST(REPLACE(ATTRIBUTE_VALUE, 'usei', '') AS DOUBLE) / 1e6 AS amount_in_sei

FROM filtered_data

WHERE ATTRIBUTE_KEY = 'amount'

SELECT

tv.validator,

SUM(va.amount_in_sei) AS total_unstake_in_sei

FROM tx_validators tv

INNER JOIN validator_amounts va

ON tv.tx_id = va.tx_id

GROUP BY tv.validator

ORDER BY total_unstake_in_sei DESC; -- Order by total unstake amounts in descending order