adriaparcerisas362 Voting activity by type of validator 3
Updated 2023-04-13
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
32
33
›
⌄
WITH
news as (
SELECT
raw_metadata[0]['account_address'] as validator_address,
min(trunc(block_timestamp,'day')) as debut
FROM osmosis.core.fact_staking
JOIN osmosis.core.dim_labels ON address = validator_address
where currency LIKE 'uosmo'
GROUP BY 1
),
proposals_info as (
SELECT
proposal_id,
voter,
block_timestamp,
tx_id
FROM osmosis.core.fact_governance_votes
),
final as (
SELECT
trunc(block_timestamp,'day') as date,
case when proposal_id =362 then 'Proposal 362' else 'Others' end as proposals,
case when datediff('day',debut,date)<30 then 'New validator'
else 'Old validator' end as type,
voter,
count(distinct tx_id) as votes
from proposals_info x
join news y on voter=validator_address
group by 1,2,3,4
)
SELECT
proposals, type,avg(votes) as avg_votes_per_voter
from final group by 1,2 order by 1 asc
Run a query to Download Data