Sbhn_NPDaily votes of new vs old validators
Updated 2022-11-09Copy Reference Fork
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
›
⌄
--credit : adriaparcerisas
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
)
SELECT
trunc(block_timestamp,'day') as date,
proposal_id,
case when datediff('day',debut,date)<30 then 'New validator'
else 'Old validator' end as type,
count(distinct voter) as voters,
count(distinct tx_id) as votes
from proposals_info x
join news y on voter=validator_address
group by 1,2,3 having proposal_id<400 order by 1 asc
Run a query to Download Data