binhachon84. [Hard] Governance Participation - Number of proposal on each voter and balance
    Updated 2021-10-11
    -- Get the data on the proposal and the proposer as well as the balance of the proposer at the proposing time
    WITH PROPOSAL_SUBMIT AS (
    SELECT BLOCK_TIMESTAMP, TX_ID, PROPOSER, PROPOSAL_ID, COALESCE(PROPOSAL_TYPE, 'NA') AS PROPOSAL_TYPE, COALESCE(SUM(BALANCE_USD),0) AS BALANCE_USD FROM terra.gov_submit_proposal LEFT JOIN terra.daily_balances
    ON DATE_TRUNC('Day', BLOCK_TIMESTAMP) = date AND PROPOSER = ADDRESS
    WHERE TX_STATUS = 'SUCCEEDED'
    GROUP BY BLOCK_TIMESTAMP, TX_ID, PROPOSER, PROPOSAL_ID, PROPOSAL_TYPE
    ),
    -- Get data on who votes on what proposal as well as what is the balance of that voter at the voting time
    PROPOSAL_VOTE AS(
    SELECT BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, VOTING_POWER, COALESCE(SUM(BALANCE_USD),0) AS BALANCE_USD FROM terra.gov_vote LEFT JOIN terra.daily_balances
    ON DATE_TRUNC('Day', BLOCK_TIMESTAMP) = date AND VOTER = ADDRESS
    WHERE TX_STATUS = 'SUCCEEDED'
    GROUP BY BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, VOTING_POWER
    ),
    -- Table contains the voter addresses as well as the total number of proposals that address has voted on
    VOTED_PROPOSALS_PER_VOTER AS(
    SELECT VOTER, COUNT(DISTINCT PROPOSAL_ID) AS NUMBER_OF_VOTED_PROPOSALS, AVG(BALANCE_USD) AS BALANCE FROM PROPOSAL_VOTE
    GROUP BY VOTER
    ORDER BY NUMBER_OF_VOTED_PROPOSALS
    ),
    PROPOSAL_VOTE_WITH_BALANCE AS(
    SELECT BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, BALANCE FROM PROPOSAL_VOTE LEFT JOIN terra.daily_balances
    ON VOTER = ADDRESS AND DATE_TRUNC('Day',BLOCK_TIMESTAMP) = date AND CURRENCY = 'LUNA'
    )
    -- Count number of addresses that have voted 1, 2, 3,... times
    SELECT NUMBER_OF_VOTED_PROPOSALS, SUM(NUMBER_OF_VOTED_PROPOSALS), AVG(BALANCE) AS BALANCE FROM VOTED_PROPOSALS_PER_VOTER
    GROUP BY NUMBER_OF_VOTED_PROPOSALS
    -- PROPOSAL_VOTE AS (
    -- SELECT BLOCK_TIMESTAMP,
    -- )


    Run a query to Download Data