mattkstewGovernance 3
    Updated 2023-02-03
    with tab1 as (
    select
    PROPOSAL_ID,
    voter as voter2,
    max(block_timestamp) as final_vote_time

    from osmosis.core.fact_governance_votes
    group by 1,2 )

    , tab2 as (
    select
    tab1.proposal_id as Proposal,
    vote_option

    from tab1 left outer join osmosis.core.fact_governance_votes
    on final_vote_time = block_timestamp
    and voter2 = voter
    )

    , tab3 as (
    select
    Proposal,
    count_if(vote_option = 1) as Yes_votes ,
    count_if(vote_option = 3) as No_votes,
    count_if(vote_option = 4) as NO_Veto_votes
    from tab2
    group by 1 )

    , tab4 as (
    select
    Proposal,
    Yes_Votes - (NO_votes + NO_Veto_votes) as Net_Vote,
    Proposal_type ,
    date_trunc('day', block_timestamp) as date

    from tab3 left outer join osmosis.core.fact_governance_submit_proposal
    Run a query to Download Data