scottincrypto70. [Hard] Validator Churn All Validators
Updated 2021-09-12Copy 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
›
⌄
with vals as (
select
event_attributes:validator::string as veloper
, vp_address as valcons
, v.label
, min(block_id) as first_block
, max(block_id) as last_block
from terra.transitions t
left join terra.validator_labels v on (t.event_attributes:validator::string = v.operator_address)
where event = 'rewards'
and block_timestamp > getdate() - interval '6 months'
and vp_address is not null
group by t.event_attributes:validator::string, v.vp_address, v.label
)
select
v.*
, f.block_timestamp as first_block_timestamp
, l.block_timestamp as last_block_timestamp
, datediff(second, f.block_timestamp, l.block_timestamp) as runtime
from vals v
left join terra.blocks f on (v.first_block = f.block_id)
left join terra.blocks l on (v.last_block = l.block_id)
Run a query to Download Data