KaskoazulxGOV Votes ALGO
    Updated 2022-03-11
    WITH xGOV_VOTES AS (
    SELECT
    block_timestamp::date as fecha,
    tx_id,
    sender,
    tx_message:rs,
    try_base64_decode_string(tx_message:txn:note::string) as note,
    CASE
    WHEN note like 'af/gov1:j[_,_a_]' then 'A'
    WHEN note like 'af/gov1:j[_,_b_]' then 'B'
    END AS VOTE
    FROM
    algorand.transactions
    WHERE
    fecha between '2022-01-31 16:59:00' and '2022-02-28'
    AND try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%'
    ),

    LATEST_VOTES_ALGO AS (
    SELECT
    max(fecha) as latest_vote,
    x.sender,
    x.tx_id,
    x.VOTE,
    --p.amount
    p.asset_amount
    FROM xGOV_VOTES x
    --INNER JOIN algorand.payment_transaction p
    INNER JOIN algorand.asset_transfer_transaction p
    ON x.tx_id = p.tx_id
    GROUP BY 2,3,4,5
    )

    SELECT
    *
    FROM LATEST_VOTES_ALGO
    Run a query to Download Data