keshanOsmosis gov 4
Updated 2022-06-18
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
›
⌄
⌄
/*
Q13. Create a dashboard that maps community participation in governance.
On average, how long does it take for a new wallet to become active in Osmosis governance?
Do wallets that are active in governance vote on every proposal? Or do they only vote on select proposals?
*/
with first_vote as (select min(block_timestamp) as first_voting, voter
from osmosis.core.fact_governance_votes
where tx_status='SUCCEEDED'
group by voter),
voting as (select voter, count(distinct proposal_id) as num_voted_props
from osmosis.core.fact_governance_votes
where tx_status='SUCCEEDED'
group by voter),
num_props as (select voter, count(distinct proposal_id) as nprops
from first_vote left join osmosis.core.fact_governance_submit_proposal on block_timestamp >= first_voting
group by voter)
select prec_cat, count(distinct voter) as voters from (select voter, case when num_voted_props > nprops then 100 else num_voted_props * 100 / (nprops+1) end as voted_percentage, -- adding 1 to nprops because when the vote registered the voted proposal has already been submitted. (an there can be multiple proposals running too that's why this has capped to 100)
case when voted_percentage = 0 then 'a. Not voted'
when voted_percentage > 0 and voted_percentage <= 10 then 'b. voted in at least 10%'
when voted_percentage > 10 and voted_percentage <= 25 then 'c. voted in 10 - 25%'
when voted_percentage > 25 and voted_percentage <= 50 then 'd. voted in 25 - 50%'
when voted_percentage > 50 and voted_percentage <= 75 then 'e. voted in 50 - 75%'
when voted_percentage > 75 and voted_percentage <= 90 then 'f. voted in 75 - 90%'
when voted_percentage > 90 and voted_percentage < 100 then 'g. voted in more than 90%'
when voted_percentage = 100 then 'h. voted in all proposals'
end as prec_cat
from num_props left join voting using(voter)
order by voted_percentage desc)
group by prec_cat
Run a query to Download Data