strawbettywallet votes over time
    Updated 2022-03-21
    with all_votes as (
    select
    sender,
    block_id,
    block_timestamp::date as date,
    try_base64_decode_string(tx_message:txn:note::string) as note,
    substr(note, position('[', note, 1)) as vote,
    substr(vote, position(',', vote, 1) + 2, 1) as selection
    from algorand.payment_transaction
    where amount is null
    and date >= '2022-01-31' and date <= '2022-02-28'
    and note like 'af/gov1:j%'
    ), last_votes as (
    SELECT DISTINCT(all_votes.sender) as sender, selection, date, block_id
    FROM (
    SELECT sender, max(block_id) as last_block
    FROM all_votes
    GROUP BY sender) as last_vote
    INNER JOIN all_votes
    ON all_votes.sender = last_vote.sender AND all_votes.block_id = last_vote.last_block
    WHERE selection IN ('a', 'b')
    )
    select date, selection, count(*) from last_votes
    group by 1, 2
    order by date
    Run a query to Download Data