select address, sum(amount) as bgt_delegated from (

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

from berachain.testnet.fact_event_logs

where contract_address=lower('0xbda130737bdd9618301681329bf2e46a016ff9ad')

and topics[0]='0x99966631dd6d6c02c5416ca2369709e025ff974a2f1b3f11c8b74acc67731f0e'

and concat('0x',substr(TOPICS[2], 27,64))=LOWER('0x34d023aca5a227789b45a62d377b5b18a680be01')

--and block_timestamp <= timestamp '2024-09-25 16:20'

group by 1

UNION ALL

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

from berachain.testnet.fact_event_logs

where contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'

and topics[0]='0xdc232a1d360a44eb299ff026b5f6badfe40d17f95b96da5db7168e88662e9a2c'

and concat('0x',substr(TOPICS[2], 27,64))=LOWER('0x34d023aca5a227789b45a62d377b5b18a680be01')

--and block_timestamp <= timestamp '2024-09-25 16:20'

group by 1

)

--deployer, kodiak deployer, foundation and bera team members

--where lower(address) not in (lower('0xac94700e79a510b885014407918805fa0fcaa6e8'),lower('0xce67e15cbcb3486b29ad44486c5b5d32f361fddc'),lower('0x34D023ACa5A227789B45A62D377b5B18A680BE01'),lower('0x4e51f68940fac327b2f018b6b79109031031fe8b'))

group by 1 having sum(amount)>=1000

),

total_delegated as (

select sum(bgt_delegated) as delegated from delegations

select address, bgt_delegated from delegations

order by bgt_delegated desc