keshanOsmosis gov 3
    Updated 2022-06-18
    /*
    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