BlockTrackersum number of vote
Updated 2023-04-20
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
›
⌄
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