nickpayiatis_2022-12-15 03:33 PM
    Updated 2022-12-16
    with raw_votes as(
    select tx_sender, block_timestamp,
    try_base64_decode_string(tx_message:txn:note::string)as votes ,
    substr(votes, 14, 1) as measure_1,
    substr(votes, 18, 1) as measure_2,
    substr(votes, 22, 1) as measure_3,
    substr(votes, 26, 1) as measure_4,
    substr(votes, 30, 1) as measure_5
    from algorand.core.ez_transaction_payment
    where
    try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:%'
    and block_timestamp::date between '2022-12-05' and '2022-12-18'
    )

    ,max_vote as(
    select tx_sender, max(block_timestamp) as block_timestamp
    from algorand.core.ez_transaction_payment

    where
    try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:%'
    and block_timestamp::date between '2022-12-05' and '2022-12-18'
    group by tx_sender
    )
    select rv.tx_sender, l.*, rv.block_timestamp, rv.votes
    from raw_votes rv
    join max_vote mv on rv.tx_sender = mv.tx_sender and rv.block_timestamp = mv.block_timestamp
    join algorand.core.dim_label l on rv.tx_sender = l.address
    and label_type = 'cex'


    Run a query to Download Data