WITH undelegation_database AS (

WITH database AS (

WITH tbl AS (

SELECT *

FROM berachain.testnet.fact_event_logs

WHERE

ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad'

AND CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad'

AND ORIGIN_FUNCTION_SIGNATURE = '0xe1f63d2e'

AND TX_SUCCEEDED = 'TRUE'

)

SELECT

block_timestamp,

tx_hash,

'0x' || SUBSTR(PARSE_JSON(TOPICS)[1]::string, -40) AS user_wallet,

'0x' || SUBSTR(PARSE_JSON(TOPICS)[2]::string, -40) AS validator_address,

utils.udf_hex_to_int(DATA) / pow(10, 18) AS undelegated_amount

FROM tbl

)

SELECT user_wallet, validator_address, SUM(undelegated_amount) AS total_undelegation

FROM database

GROUP BY 1, 2

),

delegation_database AS (

WITH database AS (

WITH tbl AS (

SELECT *

FROM berachain.testnet.fact_event_logs

WHERE

ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad'

AND CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad'

AND ORIGIN_FUNCTION_SIGNATURE = '0x95c0e232'

AND TX_SUCCEEDED = 'TRUE'

)