keshanOsmosis gov 5
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
31
32
33
34
35
36
›
⌄
⌄
/*
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),
active as (select tx_from as user, min(block_timestamp) as date from osmosis.core.fact_transactions where block_timestamp <= CURRENT_DATE - 1
group by tx_from),
passive as (select receiver as user, min(block_timestamp) as date from osmosis.core.fact_transfers where block_timestamp <= CURRENT_DATE - 1
group by receiver),
newusers as (select user, min(date) as date from (select * from active union select * from passive) group by user),
time_2_vote as (select first_voting, n.date as joined_date, n.user, datediff('hour', n.date, first_voting) as time_to_vote,
case when time_to_vote >= 0 and time_to_vote <=1 then 'a. 0-1 hour'
when time_to_vote > 1 and time_to_vote <=12 then 'b. 1-12 hours'
when time_to_vote > 12 and time_to_vote <=24 then 'c. 12-24 hours'
when time_to_vote > 24 and time_to_vote <=168 then 'd. 1 day - 1 week'
when time_to_vote > 168 and time_to_vote <=720 then 'e. 1 week - 1 month'
when time_to_vote > 720 then 'f. 1+ months' end as time_cat
from newusers n inner join first_vote f on voter = user)
select count(distinct voter) as cnt, time_cat 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)
from num_props left join voting using(voter)
order by voted_percentage desc) v left join first_vote using(voter) left join time_2_vote t on v.voter=t.user
where voted_percentage = 100
group by time_cat
Run a query to Download Data