headitmanager11osmo
Updated 2022-11-21
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
with balance_tbl as (select sum(BALANCE/pow(10,6)) as osmo_balance , ADDRESS from osmosis.core.fact_daily_balances
where CURRENCY = 'uosmo' and DATE= '2022-09-04' and BALANCE_TYPE='staked'
group by address)
, proposals_tbl as (select VOTER,VOTE_OPTION,BLOCK_TIMESTAMP,PROPOSAL_ID from osmosis.core.fact_governance_votes
where PROPOSAL_ID in (361,362,363,365) and TX_STATUS='SUCCEEDED')
, data_tbl as (select VOTER,VOTE_OPTION,BLOCK_TIMESTAMP,PROPOSAL_ID,osmo_balance from balance_tbl
inner join proposals_tbl on ADDRESS=VOTER )
, more_vote_tbl as (select VOTER , count(distinct VOTE_OPTION) as different_vote , PROPOSAL_ID from data_tbl
group by VOTER,PROPOSAL_ID
having different_vote >= 2)
select count(VOTER) as total_vote , count(distinct VOTER) as unique_voter ,
sum(osmo_balance) as total_osmo ,sum(osmo_balance)/count(distinct VOTER) as average_osmo,
PROPOSAL_ID
from data_tbl
where VOTER in (select VOTER from more_vote_tbl)
group by PROPOSAL_ID
Run a query to Download Data