100 rows yesterday at 12:57 PM
0xb2cc224c1c9fee385f8ad6a55b4d94e92359dc59 48.9998987425
0x70acdf2ad0bf2402c957154f944c19ef4e1cbae1 9.8596022882
0xc200f21efe67c7f41b81a854c26f9cda80593065 7.2188407153
0x82321f3beb69f503380d6b233857d5c43562e2d0 5.6400240491
0xb909f567c5c2bb1a4271349708cc4637d7318b4a 5.3250452187
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Step 1: Calculate total vote weight across all pools on a weekly basis
with total_votes as (
select
date_trunc('week', timestamp) as vote_week, -- Truncate timestamp to weekly intervals
sum(cast(input_4_value_uint256 as double) / 1e18) as total_vote_weight -- Total voting weight for each week
from
evm_events_base_mainnet_v1
where
address = '0x16613524e02ad97edfef371bc883f2f5d6c480a5' -- Voting contract address
and signature = 'Voted(address,address,uint256,uint256,uint256,uint256)' -- Hardcoded Voted signature
group by
date_trunc('week', timestamp) -- Group by weekly intervals
),
-- Step 2: Calculate each pool's total vote weight for each week
pool_votes as (
select
date_trunc('week', evm.timestamp) as vote_week, -- Truncate timestamp to weekly intervals
evm.input_1_value_address as pool_address, -- Pool address (voted for)
sum(cast(evm.input_4_value_uint256 as double) / 1e18) as pool_vote_weight -- Total vote weight for each pool per week
from
evm_events_base_mainnet_v1 evm
where
evm.address = '0x16613524e02ad97edfef371bc883f2f5d6c480a5' -- Voting contract address
and evm.signature = 'Voted(address,address,uint256,uint256,uint256,uint256)' -- Hardcoded Voted signature
group by
date_trunc('week', evm.timestamp), -- Group by weekly intervals
evm.input_1_value_address -- Group by pool address
)
-- Step 3: Calculate the vote percentage for each pool in the most recent week
select
pv.vote_week, -- Week of the votes
pv.pool_address as chart_x, -- Pool address
pv.pool_vote_weight, -- Total vote weight for the pool in that week
tv.total_vote_weight as total_weekly_vote_weight, -- Total vote weight across all pools in that week
(pv.pool_vote_weight / tv.total_vote_weight) * 100 as chart_y -- Correctly calculate the vote percentage of the pool
from
pool_votes pv
join
total_votes tv on pv.vote_week = tv.vote_week -- Join on the same week to get the total vote weight
where
pv.vote_week = ( -- Filter to the most recent week
select max(date_trunc('week', timestamp))
from evm_events_base_mainnet_v1
where address = '0x16613524e02ad97edfef371bc883f2f5d6c480a5' -- Voting contract address
and signature = 'Voted(address,address,uint256,uint256,uint256,uint256)'
)
order by
pv.pool_vote_weight desc -- Order by total vote weight (largest to smallest)
limit 100;