keshanOsmosis gov 5
    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 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