KuramaOsmosis Decentralizing Power Q1 - Proposal 114 - Amount delegated by the time of the proposal + 30
    Updated 2022-11-02
    -- 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