hess2.1
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 (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_options
from cosmos.core.fact_msg_attributes a join proposals b on a.tx_id = b.tx_id
where ATTRIBUTE_KEY = 'option'
and proposal_id = 82
)
,
receiver as ( select receiver , sum(amount/pow(10,6)) as total_receive
from cosmos.core.fact_transfers
where currency = 'uatom'
and TX_SUCCEEDED = 'TRUE'
group by 1)
,
sender as ( select sender , sum(amount/pow(10,6)) as total_send
from cosmos.core.fact_transfers
where currency = 'uatom'
and TX_SUCCEEDED = 'TRUE'
group by 1)
,
current_balance as ( select sender, total_receive , total_send , total_receive-total_send as currenct_balance
from receiver a join sender b on a.receiver = b.sender)
select count(DISTINCT(voter)) as total,
case when currenct_balance <= 1 then 'Below 1 Atom'
when currenct_balance <= 10 then '1-10 Atom'
when currenct_balance <= 100 then '10-100 Atom'
when currenct_balance <= 1000 then '100-1K Atom'
when currenct_balance <= 10000 then '1k-10K Atom'
when currenct_balance <= 100000 then '10K-100K Atom'
Run a query to Download Data