hess1-2.
Updated 2022-12-11
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 proposals as ( select date(a.block_timestamp) as date,TX_FROM as voter, a.tx_id, ATTRIBUTE_VALUE as proposal_id
from cosmos.core.fact_msg_attributes a join cosmos.core.fact_transactions b on a.tx_id = b.tx_id
where b.TX_SUCCEEDED = 'TRUE' and ATTRIBUTE_KEY = 'proposal_id')
,
vote_option as ( select date, voter, a.tx_id, proposal_id,try_parse_json (attribute_value) option,
option:option as vote_option, case when vote_option = 1 then 'YES'
when vote_option = 2 then 'Abstain'
when vote_option = 3 then 'No'
when vote_option = 4 then 'No With Veto' end as vote_option
from cosmos.core.fact_msg_attributes a join proposals b on a.tx_id = b.tx_id
where ATTRIBUTE_KEY = 'option'
)
,
validator_vote_option as ( select left(voter,35) as voters , b.tx_id
from cosmos.core.fact_msg_attributes a join proposals b on a.tx_id = b.tx_id
where ATTRIBUTE_KEY = 'option'
)
,
validator as ( select REPLACE(ATTRIBUTE_VALUE, 'valoper', '') as validator , left(validator, 35) as validators
from cosmos.core.fact_msg_attributes
where ATTRIBUTE_KEY = 'validator')
,
validator_voter as ( select tx_id, validators
from validator a join validator_vote_option b on a.validators = b.voters)
,
validator_votes as ( select date , voter, a.tx_id, proposal_id,try_parse_json (attribute_value) option,
option:option as vote_option, case when vote_option = 1 then 'YES'
when vote_option = 2 then 'Abstain'
when vote_option = 3 then 'No'
when vote_option = 4 then 'No With Veto' end as vote_option
from cosmos.core.fact_msg_attributes a join proposals b on a.tx_id = b.tx_id
where ATTRIBUTE_KEY = 'option'
and a.tx_id in (select tx_id from validator_voter)
)
select 'Validators' as type, date, count(DISTINCT(voter)) as total_voter, count(DISTINCT(tx_id)) as total_votes
Run a query to Download Data