CryptoLionvoting2
    Updated 2021-08-11
    WITH x as (
    SELECT
    sum(CASE WHEN action <> 'undelegate' THEN event_amount ELSE 0 END) as delegated_LUNA,
    sum(CASE WHEN action = 'undelegate' THEN event_amount ELSE 0 END) as undelegated_LUNA,
    delegated_LUNA - undelegated_LUNA as total_LUNA
    FROM terra.staking
    WHERE tx_status = 'SUCCEEDED'
    LIMIT 1
    ),

    -- SELECT *
    -- FROM x
    y as (
    SELECT
    validator_address_label,
    count(DISTINCT delegator_address) as delegators,
    sum(CASE WHEN action <> 'undelegate' THEN event_amount ELSE 0 END) as delegated_LUNA,
    sum(CASE WHEN action = 'undelegate' THEN event_amount ELSE 0 END) as undelegated_LUNA,
    delegated_LUNA - undelegated_LUNA as LUNA
    FROM terra.staking
    WHERE tx_status = 'SUCCEEDED'
    GROUP BY 1
    ORDER BY 5 DESC
    LIMIT 10
    ),
    z as (
    SELECT
    validator_address_label,
    count(DISTINCT tx_id) as votes
    FROM y
    INNER JOIN terra.gov_vote on validator_address_label = voter_address_label
    GROUP BY 1
    ORDER BY 2 DESC
    ),
    a as (
    SELECT