delegateVotesChanged_txs as (

select

block_timestamp

, tx_hash

, concat('0x', right(topics[1], 40)) as delegate

, utils.udf_hex_to_int(substr(data, 67, 64)) ::int / pow(10, 18) as new_votes

, utils.udf_hex_to_int(substr(data, 3, 64)) ::int / pow(10, 18) as previous_votes

, new_votes - previous_votes as change_votes

, 'Ethereum' as chain

from ethereum.core.fact_event_logs

where tx_status = 'SUCCESS'

and contract_address = '0xb0ffa8000886e57f86dd5264b9582b2ad87b2b91'

and topics[0] = '0xdec2bacdd2f05b59de34da9b523dff8be42e5e38e818c82fdb0bae774387a724' -- DelegateVotesChanged

and block_timestamp >= '2024-04-17'

qualify row_number() over (partition by delegate order by block_timestamp desc, event_index desc) = 1

union all

select

block_timestamp

, tx_hash

, concat('0x', right(topics[1], 40)) as delegate

, utils.udf_hex_to_int(substr(data, 67, 64)) ::int / pow(10, 18) as new_votes

, utils.udf_hex_to_int(substr(data, 3, 64)) ::int / pow(10, 18) as previous_votes

, new_votes - previous_votes as change_votes

, 'Arbitrum' as chain

from arbitrum.core.fact_event_logs

where tx_status = 'SUCCESS'

and contract_address = '0xb0ffa8000886e57f86dd5264b9582b2ad87b2b91'