Crazy_KidAlgorand Governance Period 2 Voting 1
    Updated 2022-03-17
    WITH 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 date, COUNT(tx_id) as no_of_votes
    FROM recent_votes
    GROUP BY date
    ORDER BY date
    Run a query to Download Data