BlockTrackersum number of vote
    Updated 2023-04-20
    with number_of_votes as (
    SELECT date_trunc('day',block_timestamp) as date,
    COUNT(DISTINCT CASE WHEN vote_option = '1' then tx_id END) as yes_option,
    COUNT(DISTINCT CASE WHEN vote_option = '2' then tx_id END) as Abstain_option,
    COUNT(DISTINCT CASE WHEN vote_option = '3' then tx_id END) as No_option,
    COUNT(DISTINCT CASE WHEN vote_option = '4' THEN tx_id END) as NoWithVeto_option,
    COUNT(DISTINCT CASE WHEN vote_option = '5' then tx_id END) as UNSPECIFIED,
    CASE WHEN date BETWEEN '2022-09-30' AND '2022-12-31' THEN 'Q4_2022'
    WHEN date BETWEEN '2023-01-01' AND '2023-04-01' THEN 'Q1_2023'
    ELSE 'Q' END as quarter
    FROM osmosis.core.fact_governance_votes
    WHERE proposal_id not in (2226,1304, 1000, 719) and vote_option != '5'
    GROUP BY date
    ORDER BY date DESC
    )
    SELECT quarter,
    sum(yes_option) as total_yes_option,
    sum(Abstain_option) as total_Abstain_option,
    sum(No_option) as total_No_option,
    sum(NoWithVeto_option) as total_NoWithVeto_option,
    sum(UNSPECIFIED) as total_UNSPECIFIED
    FROM number_of_votes
    GROUP BY quarter