shadilValidator Voting vs. Investor Inclinations
Updated 2022-04-27
99
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
›
⌄
with latest_props as (
select
proposal_id::INTEGER as id
from terra.gov_vote
where id <= 971 -- last passed proposal
group by id
order by id DESC
limit 5
),
validators as ( -- find validators
select address, operator_address, delegator_address, voting_power
from terra.validator_voting_power vop
left join terra.validator_labels lb on vop.address = lb.vp_address
where block_timestamp = (select max(block_timestamp) from terra.validator_voting_power)
order by voting_power desc
),
validator_votes as ( -- get validators votes
select a.proposal_id,
sum(b.voting_power) as validators_votes,
avg(b.voting_power) as avg_validators_votes
from terra.gov_vote a inner join validators b on a.voter = b.delegator_address
where tx_status = 'SUCCEEDED'
and a.proposal_id in (SELECT id from latest_props)
group by a.proposal_id
),
retailers as ( -- get retailers votes
select a.proposal_id, sum(b.balance) as wallet_votes, avg(b.balance) as avg_wallet_votes
from terra.gov_vote a
left join terra.daily_balances b on a.voter = b.address
and b.date = (select max(date) from terra.daily_balances)
and b.currency = 'LUNA'
where tx_status = 'SUCCEEDED'
and a.proposal_id in (SELECT id from latest_props)
group by a.proposal_id
)
Run a query to Download Data