Crazy_KidAlgorand Governance Period 2 Voting 2
    Updated 2022-03-17
    WITH commitment AS (SELECT DATE(block_timestamp) as date, sender as committer, tx_id,
    SUBSTRING(try_base64_decode_string(tx_message:txn:note), 17, len(try_base64_decode_string(tx_message:txn:note))-17) as
    commitment_amount
    FROM algorand.payment_transaction
    WHERE date(block_timestamp) BETWEEN '2021-12-24' AND '2022-01-07'
    AND try_base64_decode_string(tx_message:txn:note) like 'af/gov1:j{"com%'
    AND receiver ='57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'
    GROUP BY date, committer, tx_id, commitment_amount
    ORDER BY date),

    recent_commitment AS (SELECT * FROM ( SELECT *, row_number() over (partition BY committer order BY date DESC) r FROM commitment ) T
    WHERE T.r=1
    ORDER BY date),

    vote AS (SELECT DATE(block_timestamp) as date, sender as voter, tx_id,
    SUBSTRING(try_base64_decode_string(tx_message:txn:note::string), 14, 1) as vote_option
    --Syntax for SUBSTRING() function:SUBSTRING(expression, starting_position, length)
    FROM algorand.payment_transaction
    WHERE date(block_timestamp) BETWEEN '2022-01-31' AND '2022-02-28'
    AND try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%' AND vote_option IN ('a', 'b')
    AND receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'
    GROUP BY date, voter, tx_id, vote_option
    ORDER BY date),

    recent_votes AS (SELECT * FROM ( SELECT *, row_number() over (partition BY voter order BY date DESC) r FROM vote ) T
    WHERE T.r=1
    ORDER BY date)

    SELECT vote_option, COUNT(voter) as votes, sum(commitment_amount)/1000000 as committed_amount
    FROM recent_commitment JOIN recent_votes ON committer = voter
    WHERE commitment_amount not LIKE '%bnf%' AND commitment_amount not LIKE '%}%' AND commitment_amount not LIKE '%:%'
    GROUP BY vote_option
    ORDER BY vote_option
    Run a query to Download Data