MadiTop 10 Validators by amount of Staked ATOM on November, 14
Updated 2022-12-11Copy 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
30
31
32
33
34
35
36
›
⌄
with t1 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'create_validator' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
t2 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'create_validator' and ATTRIBUTE_KEY = 'validator' and TX_SUCCEEDED = 'TRUE'),
t3 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount
from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'delegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
t4 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'delegate' and ATTRIBUTE_KEY = 'validator' and TX_SUCCEEDED = 'TRUE'),
t5 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6) as amount
from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
t6 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'destination_validator' and TX_SUCCEEDED = 'TRUE'),
t7 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, replace(ATTRIBUTE_VALUE, 'uatom')/pow(10,6)*-1 as amount
from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'amount' and TX_SUCCEEDED = 'TRUE'),
t8 as (select date_trunc('day', BLOCK_TIMESTAMP) as date, tx_id, ATTRIBUTE_VALUE as validator from cosmos.core.fact_msg_attributes
where MSG_TYPE= 'redelegate' and ATTRIBUTE_KEY = 'source_validator' and TX_SUCCEEDED = 'TRUE'),
income1 as (select t1.date, validator, amount from t1 join t2 on t1.tx_id = t2.tx_id),
income2 as (select t3.date, validator, amount from t3 join t4 on t3.tx_id = t4.tx_id),
income3 as (select t5.date, validator, amount from t5 join t6 on t5.tx_id = t6.tx_id),
outcome as (select t7.date, validator, amount from t7 join t8 on t7.tx_id = t8.tx_id),
balance as (
select date, validator, sum(amount) as amount_atom from (
select * from income1 UNION ALL select * from income2 UNION ALL select * from income3 UNION ALL select * from outcome)
group by 1,2)
Run a query to Download Data