keshanAlgorand Governance Period 2 Voting
    Updated 2022-03-06
    with voters as (
    select sender, max(block_timestamp) as max_date
    from algorand.payment_transaction
    where date_trunc('minute', block_timestamp) > '2022-1-31 15:59:00' -- voting commenced 31st Jan 15:59 UTC
    and date_trunc('minute', block_timestamp) < '2022-2-28 16:00:00'
    and receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'
    and try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%'
    group by sender),
    committers as (
    select sender, max(block_timestamp) as max_date
    from algorand.payment_transaction
    where date_trunc('day', block_timestamp) > '2021-12-24' -- committing commenced 24th Dec
    and date_trunc('day', block_timestamp) < '2022-1-7'
    and try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j{"com%'
    group by sender
    ),
    commitments as (
    select c.sender, c.max_date as committed_date,
    try_cast(REGEXP_SUBSTR(try_base64_decode_string(t.tx_message:txn:note::string), '(\\d+)\\w') as BIGINT) as comt,
    (case when comt is null then 0 else comt / 1e6 end) as amount
    from committers c, algorand.payment_transaction t
    where c.sender = t.sender and c.max_date = t.block_timestamp
    ),
    votes as (
    select v.sender, v.max_date, REGEXP_SUBSTR(try_base64_decode_string(t.tx_message:txn:note::string), '"[ab]"') as vote, c.amount
    from voters v, algorand.payment_transaction t, commitments c
    where v.sender = t.sender and v.max_date = t.block_timestamp and c.sender = v.sender)
    select max_date::date as date, sum(amount) as algos,
    count(distinct sender) as all_votes,
    sum(case when vote = '"a"' then 1 else 0 end) as A, sum(case when vote = '"b"' then 1 else 0 end) as B,
    sum(case when vote = '"a"' then amount else 0 end) as algos_voted_for_A, sum(case when vote = '"b"' then amount else 0 end) as algos_voted_for_B,
    (100* A/ all_votes) as a_precentage, (100* B/ all_votes) as b_precentage
    from votes group by date
    Run a query to Download Data