with base as (select sender, try_base64_decode_string(tx_message:txn:note::string) as vote, CASE when vote in ('af/gov1:j[5,″a″]','af/gov1:j[5,"a"]','af/gov1:j[5, "a"]','af/gov1:j[5,a]','af/gov1:j[5,"a"]"','af/gov1:j[5,"a”]','af/gov1:j[5,"a" ]','af/gov1:j[5,"a"','af/gov1:j[5,"a"]', 'af/gov1:j[5,"A"]','af/gov1:j[5,"a"]','af/gov1:j[5,”a”]','af/gov1:j[3,"a"]','af/gov1:j[5,"a','af/gov1:j[5,′′a′′]') then 'A' else 'B' end as votes, block_timestamp -- max(block_timestamp) as voting_date from algorand.transactions where --tx_id = 'GDH6IKWWPYZ4R3STQFSOMGVO6LN2UG4KOTGVFJOH7XKUUC4BHH6A' block_timestamp::date >= '2022-01-31' and block_timestamp::date <= '2022-02-28' and try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%'), base2 as (select sender, max(block_timestamp) as latest_vote from base group by 1), base3 as (select b.sender, latest_vote, votes from base2 a join base b on (a.sender = b.sender and a.latest_vote = b.block_timestamp)), commit as (select sender, max(block_timestamp) as latest_commit from algorand.payment_transaction where try_base64_decode_string(tx_message:txn:note::string) ilike '%af/gov1:j{"com%' and receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'-- governance period 2 receiver address and block_timestamp >= '2021-12-24' and block_timestamp < '2022-01-08' group by 1), base4 as (select a.sender, try_base64_decode_string(tx_message:txn:note::string) as commit_amount, substring(commit_amount,17) as part1, substring(part1, 1, charindex('}', part1)-1) as part2, try_to_number(part2)/1e6 as algos_committed from algorand.payment_transaction a join commit b on (a.sender = b.sender and a.block_timestamp = b.latest_commit) where try_base64_decode_string(tx_message:txn:note::string) ilike '%af/gov1:j{"com%' and receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY' and block_timestamp >= '2021-12-24' and block_timestamp < '2022-01-08'), --and tx_id = 'LYVTA4PM4UWLL3GGCHIXDCZXKW2XVJ67ZZWPIXNCKTG7JF2YQT2A' base5 as (select a.sender, algos_committed, case when algos_committed > 0 and algos_committed <= 10 then 'algos committed > 0 and <= 10' when algos_committed > 10 and algos_committed <= 100 then 'algos committed > 10 and <= 100' when algos_committed > 100 and algos_committed <= 1000 then 'algos committed > 100 and <= 1,000' when algos_committed > 1000 and algos_committed <= 10000 then 'algos committed > 1,000 and <= 10,000' when algos_committed > 10000 and algos_committed <= 100000 then 'algos committed > 10,000 and <= 100,000' when algos_committed > 100000 and algos_committed <= 1000000 then 'algos committed > 100,000 and <= 1,000,000' -- when algos_committed > 10000000 and algos_committed <= 100000000 then 'algos committed > 10M and <= 100M' else 'algos committed > 1000,000' end as algos_committed_ranges, case when algos_committed > 0 and algos_committed <= 10 then '1' when algos_committed > 10 and algos_committed <= 100 then '2' when algos_committed > 100 and algos_committed <= 1000 then '3' when algos_committed > 1000 and algos_committed <= 10000 then '4' when algos_committed > 10000 and algos_committed <= 100000 then '5' when algos_committed > 100000 and algos_committed <= 1000000 then '6' -- when algos_committed > 10000000 and algos_committed <= 100000000 then '8' else '7' end as ordering, votes from base4 a join base3 c on a.sender = c.sender) select votes, algos_committed_ranges, ordering, count(distinct(sender)) as total_voters from base5 group by 1,2,3 order by 3,1 asc

Algorand Governance Period 2 Voting