CryptoLiondelegated LUNA
    Updated 2022-07-07
    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
    )
    SELECT
    validator_address_label,
    delegators,
    LUNA,
    LUNA/total_LUNA*100 as percentage_luna
    FROM x
    JOIN y
    ORDER BY 3 DESC

    Run a query to Download Data