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,

DATE_TRUNC('day', block_timestamp) AS day,

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

WHERE

user_wallet != '0xce67e15cbcb3486b29ad44486c5b5d32f361fddc'

)

SELECT

user_wallet,

validator_address,

day,

SUM(undelegated_amount) AS total_undelegation

FROM

database

GROUP BY

user_wallet,

validator_address,

day