xkage10 - average daily votes per voter
Updated 2022-11-21
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
25
26
27
28
29
30
31
›
⌄
WITH validators_list AS (
SELECT
Address AS valid_ad,
label AS valid_name,
raw_metadata[0]['account_address']::string AS Account_Ad
FROM osmosis.core.dim_labels
WHERE LABEL_SUBTYPE = 'validator')
SELECT
date_trunc('day', block_timestamp) as DATE,
'regular' AS voter_type,
(COUNT(DISTINCT tx_id) - COUNT(DISTINCT CASE WHEN voter IN (SELECT Account_Ad FROM validators_list) THEN tx_id END)) AS n_votes,
(COUNT(DISTINCT voter) - COUNT(DISTINCT CASE WHEN voter IN (SELECT Account_Ad FROM validators_list) THEN voter END)) AS n_voter,
n_votes/n_voter AS avg_votes_per_voter
FROM osmosis.core.fact_governance_votes
WHERE proposal_id = '362' AND tx_status = 'SUCCEEDED'
GROUP BY 1,2
UNION ALL
SELECT
date_trunc('day', block_timestamp) as DATE,
'validator' AS voter_type,
COUNT(DISTINCT CASE WHEN voter IN (SELECT Account_Ad FROM validators_list) THEN tx_id END) AS n_Votes,
COUNT(DISTINCT CASE WHEN voter IN (SELECT Account_Ad FROM validators_list) THEN voter END) AS n_Voter,
n_votes/n_voter AS avg_votes_per_voter
FROM osmosis.core.fact_governance_votes
WHERE proposal_id = '362' AND tx_status = 'SUCCEEDED'
GROUP BY 1,2
ORDER BY 1 ASC
Run a query to Download Data