binhachon84. [Hard] Governance Participation - Distribution
    Updated 2021-10-10
    WITH GOVERNANCE_STAKING AS(
    SELECT EVENT_ATTRIBUTES:"from"::STRING AS ADDRESS, SUM(EVENT_ATTRIBUTES:"0_amount"/1e6) AS AMOUNT from terra.msg_events
    WHERE EVENT_TYPE = 'from_contract'
    AND TX_STATUS = 'SUCCEEDED'
    AND EVENT_ATTRIBUTES:"1_action"::STRING = 'staking'
    AND EVENT_ATTRIBUTES:"1_contract_address"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
    GROUP BY ADDRESS
    ),
    GOVERNANCE_UNSTAKING AS (SELECT MSG_VALUE:"sender"::string AS ADDRESS, SUM(MSG_VALUE:"execute_msg":"withdraw_voting_tokens":"amount"::float/1e6) AS AMOUNT FROM terra.msgs
    WHERE MSG_VALUE:"contract"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
    AND MSG_VALUE:"execute_msg":"withdraw_voting_tokens":"amount" IS NOT NULL
    AND TX_STATUS = 'SUCCEEDED'
    GROUP BY ADDRESS),
    GOVERNANCE_VOTING AS (
    SELECT MSG_VALUE:"sender"::string as ADDRESS, COUNT(DISTINCT MSG_VALUE:"execute_msg":"cast_vote":"poll_id"::float) AS VOTE_AMOUNT FROM terra.msgs
    WHERE MSG_VALUE:"contract"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
    AND MSG_VALUE:"execute_msg":"cast_vote":"amount" IS NOT NULL
    GROUP BY ADDRESS
    ),
    FINAL_TABLE AS(
    SELECT GOVERNANCE_STAKING.ADDRESS AS ADDRESS, GOVERNANCE_STAKING.AMOUNT AS STAKING_AMOUNT, GOVERNANCE_UNSTAKING.AMOUNT AS UNSTAKING_AMOUNT,
    GOVERNANCE_STAKING.AMOUNT - GOVERNANCE_UNSTAKING.AMOUNT AS FINAL_AMOUNT, VOTE_AMOUNT
    FROM GOVERNANCE_STAKING
    LEFT JOIN GOVERNANCE_UNSTAKING
    ON GOVERNANCE_STAKING.ADDRESS = GOVERNANCE_UNSTAKING.ADDRESS
    LEFT JOIN GOVERNANCE_VOTING
    ON GOVERNANCE_STAKING.ADDRESS = GOVERNANCE_VOTING.ADDRESS
    )
    SELECT VOTE_AMOUNT, COUNT(VOTE_AMOUNT) FROM GOVERNANCE_VOTING
    GROUP BY VOTE_AMOUNT


    Run a query to Download Data