KuramaOsmosis Decentralizing Power Q1 - Proposal 114 - Amount delegated by the time of the proposal + 30
Updated 2022-11-02Copy 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
›
⌄
-- this first table contains, for each validator, the delegations and undelegations
with delegations_undelegations as (select validator_address, action, sum(amount/pow(10,decimal)) as total_amount, case when action = 'delegate' then total_amount else total_amount*(-1) end as total_amount_sign from osmosis.core.fact_staking
where action in ('delegate','undelegate')
and tx_status = 'SUCCEEDED'
and to_date(block_timestamp) <= '2022-01-28'
group by validator_address, action ),
-- redelegate_soure_validator_address is the validator you were previously redelegating, and validator_address is the validator to whom you are now redelegating
redelegations_from as (select validator_address, sum(amount/pow(10,decimal)) as total_amount_sign from osmosis.core.fact_staking
where action = 'redelegate'
and tx_status = 'SUCCEEDED'
and to_date(block_timestamp) <= '2022-01-28'
group by validator_address
),
redelegations_to as (select redelegate_source_validator_address as validator_address, sum((amount*(-1))/pow(10,decimal)) as total_amount_sign from osmosis.core.fact_staking
where action = 'redelegate'
and tx_status = 'SUCCEEDED'
and to_date(block_timestamp) <= '2022-01-28'
group by redelegate_source_validator_address
),
-- the following table returns, for each validator, amount delegated, redelegated to, and undelegated from with the respective sign
final_table as
(select validator_address, total_amount_sign from delegations_undelegations
union all
select * from redelegations_from
union all
select * from redelegations_to
),
-- This table groups all validator per amunt, and since signs were already correctly taken into account before, it's all gut
last_table as (
select validator_address, sum(total_amount_sign) as total_amount_delegated from final_table
group by validator_address),
Run a query to Download Data