jp128 [OSMO] Staking - Validators
Updated 2022-05-28
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
›
⌄
WITH stake as (
SELECT a.block_timestamp::date as date, a.tx_id, b.ATTRIBUTE_VALUE as validator, split_part(a.ATTRIBUTE_VALUE, 'uosmo', 1) / 1e6 as amount
FROM osmosis.core.fact_msg_attributes a INNER JOIN osmosis.core.fact_msg_attributes b ON a.tx_id = b.tx_id
WHERE a.msg_type = 'delegate' and a.attribute_key = 'amount' and b.attribute_key = 'validator'
UNION
SELECT a.block_timestamp::date as date, a.tx_id, b.ATTRIBUTE_VALUE as validator, split_part(a.ATTRIBUTE_VALUE, 'uosmo', 1) / -1e6 as amount
FROM osmosis.core.fact_msg_attributes a INNER JOIN osmosis.core.fact_msg_attributes b ON a.tx_id = b.tx_id
WHERE a.msg_type = 'unbond' and a.attribute_key = 'amount' and b.attribute_key = 'validator'
UNION
SELECT a.block_timestamp::date as date, a.tx_id, b.ATTRIBUTE_VALUE as validator, split_part(a.ATTRIBUTE_VALUE, 'uosmo', 1) / 1e6 as amount
FROM osmosis.core.fact_msg_attributes a INNER JOIN osmosis.core.fact_msg_attributes b ON a.tx_id = b.tx_id
WHERE a.msg_type = 'redelegate' and a.attribute_key = 'amount' and b.attribute_key = 'destination_validator'
UNION
SELECT a.block_timestamp::date as date, a.tx_id, b.ATTRIBUTE_VALUE as validator, split_part(a.ATTRIBUTE_VALUE, 'uosmo', 1) / -1e6 as amount
FROM osmosis.core.fact_msg_attributes a INNER JOIN osmosis.core.fact_msg_attributes b ON a.tx_id = b.tx_id
WHERE a.msg_type = 'redelegate' and a.attribute_key = 'amount' and b.attribute_key = 'source_validator'
)
SELECT validator, SUM(amount) as osmo_staked
FROM stake
GROUP BY 1
ORDER BY 2 DESC t
Run a query to Download Data