SELECT validator_name , address

FROM (

VALUES

('The-Honey-Jar','0x40495A781095932e2FC8dccA69F5e358711Fdd41'),

('Infrared','0x2D764DFeaAc00390c69985631aAA7Cc3fcfaFAfF'),

('Kodiak Finance','0x0eCBe62654622e14ae882B8c8c65C3f3F54eCcf9'),

('BeraLand','0x35c1e9C7803b47aF738f37Beada3C7c35Eed73d4'),

('StakeLab','0xC5b889a28950e7F8c1F279f758d8a0ab1C89cC38'),

('KingNodes','0x19Bfe7b58D3D2C63Ee082A1C1db33F970Ca1fA44'),

('beradrome-x-thj','0x34D023ACa5A227789B45A62D377b5B18A680BE01'),

('KudasaiJP','0xC09A619A872c56C8de1354A4309aBBF317938084'),

('Manticore','0x2Ecd703B119C3FbFb2ec4b9FFa6d8756f7De9428'),

('DOUBLETOP','0xe868bE65C50b61E81A3fC5cB5A7916090B05eb2A'),

('WhisperNode','0xeC6d3f350BcdCe0F451D52e69f61Bf3630240B66')

) AS X(validator_name , address)

),

delegate as (

select validator,address, sum(amount) as delegated from (

select concat('0x',substr(TOPICS[2], 27,64)) as validator,concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount

from berachain.testnet.fact_event_logs

where contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and topics[0]='0x99966631dd6d6c02c5416ca2369709e025ff974a2f1b3f11c8b74acc67731f0e'

and concat('0x',substr(TOPICS[2], 27,64)) IN (select lower(address) from data)

)

group by 1,2

),

unbond as (

select validator,address, sum(amount) as unbonded from (

select concat('0x',substr(TOPICS[2], 27,64)) as validator,concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount

from berachain.testnet.fact_event_logs

where contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and ORIGIN_FUNCTION_SIGNATURE='0xe1f63d2e'

and concat('0x',substr(TOPICS[2], 27,64)) IN (select lower(address) from data)

)

group by 1,2