keshanOsmosis gov 3
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
›
⌄
⌄
/*
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 *, case when num_voted_props=1 then 'a. Only 1'
when num_voted_props > 1 and num_voted_props <=10 then 'b. 1 to 10'
when num_voted_props > 10 and num_voted_props <=50 then 'c. 10 to 50'
when num_voted_props > 50 and num_voted_props <=100 then 'd. 50 to 100'
when num_voted_props > 100 and num_voted_props <=200 then 'e. 100 to 200'
when num_voted_props > 200 and num_voted_props <=250 then 'f. 200 to 250'
when num_voted_props > 250 then 'g. 250 or more' end as vote_cat from(
select voter, count(distinct proposal_id) as num_voted_props
from osmosis.core.fact_governance_votes
where tx_status='SUCCEEDED'
group by voter)
order by vote_cat)
select vote_cat, count(voter) as count from voting group by vote_cat