1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

-- Step 1: Calculate total vote weight across all pools

with total_votes as (

select

sum(cast(input_4_value_uint256 as double) / 1e18) as total_vote_weight -- Total voting weight across all pools (human-readable)

from

evm_events_base_mainnet_v1

where

address = '0x16613524e02ad97edfef371bc883f2f5d6c480a5' -- Voting contract address

and signature = 'Voted(address,address,uint256,uint256,uint256,uint256)' -- Hardcoded Voted signature

)

-- Step 2: Calculate each pool's total vote weight and its percentage of the total

select

evm.input_1_value_address as chart_x, -- Pool address (voted for)

sum(cast(evm.input_4_value_uint256 as double) / 1e18) as total_vote_weight, -- Total vote weight for each pool

(sum(cast(evm.input_4_value_uint256 as double) / 1e18) / tv.total_vote_weight) * 100 as chart_y, -- Vote weight as a percentage of total votes

max(evm.timestamp) as latest_vote_timestamp -- Latest vote timestamp

from

evm_events_base_mainnet_v1 evm

cross join

total_votes tv -- Cross join to access total vote weight

where

evm.address = '0x16613524e02ad97edfef371bc883f2f5d6c480a5' -- Voting contract address

and evm.signature = 'Voted(address,address,uint256,uint256,uint256,uint256)' -- Hardcoded Voted signature

group by

evm.input_1_value_address, tv.total_vote_weight -- Group by pool address and total vote weight

order by

total_vote_weight desc -- Order by total vote weight (largest to smallest)

limit 100;