binhachon3. LUNA Validators - Compare
    Updated 2021-11-01
    with pylon_governance as(
    select block_timestamp, msg_value:sender::string as address, 0 as deposit_amount, msg_value:execute_msg:withdraw_voting_tokens:amount::float/1e6 as withdraw_amount from terra.msgs
    where msg_value:contract::string = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
    and msg_value:execute_msg:withdraw_voting_tokens:amount is not null
    union all
    select block_timestamp, msg_value:sender::string as address, msg_value:execute_msg:send:amount::float/1e6 as deposit_amount, 0 as withdraw_amount from terra.msgs
    where msg_value:contract::string = 'terra1kcthelkax4j9x8d3ny6sdag0qmxxynl3qtcrpy'
    and msg_value:execute_msg:send:contract::string = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
    ),
    address_list as(
    select address from (
    select address, sum(deposit_amount) as deposit_amount, sum(withdraw_amount) as withdraw_amount, sum(deposit_amount) - sum(withdraw_amount) as net_deposit_amount from pylon_governance
    group by address
    )
    where net_deposit_amount > 0
    ),
    luna_staker as(
    select distinct delegator_address, validator_address,
    case when validator_address = 'terravaloper1jkg3wy5q9q6jlshjf2r6p9nf4flwtr6hp30rjk' then 'Neptune Finance'
    when validator_address = 'terravaloper1mgdsc0get3w984h03a02zy6gmg3kgqtfqs3tky' then 'LambdaCore' else validator_address_label end as validator_address_label,
    validator_address_name, validator_label_subtype, validator_label_type from terra.staking
    where delegator_address in (select address from address_list)
    and action in ('delegate', 'redelegate')
    ),
    total_staker as(
    select validator_address as validator, count(distinct delegator_address) as total_staker from terra.staking
    where action in ('delegate', 'redelegate')
    group by validator
    )
    select validator_address, validator_address_label, number_of_addresses * 100 / sum(number_of_addresses) over (), total_staker * 100 / sum(total_staker) over () from(
    select validator_address, validator_address_label, number_of_addresses, row_number() over (order by number_of_addresses desc) as rownumber from(
    select validator_address, validator_address_label, count(delegator_address) as number_of_addresses from luna_staker
    group by validator_address, validator_address_label
    order by number_of_addresses desc
    )
    ) left join total_staker on validator_address = validator
    Run a query to Download Data