-- Algorand Governance Period 2 Voting -- Question 34: Algorand's 2nd Governance Voting Period Ran From January 31st to February 28th 2022 -- (https://governance.algorand.foundation/governance-period-2), lets take a look at how wallets voted for or against xGovs. -- Payout 91.74 ALGO -- Grand Prize 275.22 ALGO -- Level Intermediate -- Show wallet votes over time -- Which had more individual wallet votes, A or B? -- Show the voting result(A vs B) based on the ALGOs committed by wallets for this governance period. -- Tips: -- Decode the note: WHERE try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%' -- Make sure to only look at transactions between the voting days -- Remember a wallet can vote more than once, take a wallets most recent vote. -- Example Voting Transaction: https://algoexplorer.io/tx/GDH6IKWWPYZ4R3STQFSOMGVO6LN2UG4KOTGVFJOH7XKUUC4BHH6A -- Committed Votes Tips: -- Here is an Algorand Governance Commitment Transaction: https://algoexplorer.io/tx/LYVTA4PM4UWLL3GGCHIXDCZXKW2XVJ67ZZWPIXNCKTG7JF2YQT2A -- Decode the notes section of the transaction to look at the governance commitment amount try_base64_decode_string(tx_message:txn:note::string) -- Remember to take the wallets most recent commitment since I wallet can modify their commitment with parsed_algo_txns as ( select tx_id, block_timestamp, TRY_BASE64_DECODE_STRING(tx_message:txn:note) as note, SPLIT(note, ':j') as note_split, try_parse_json(GET(note_split,1)) as parsed_json, sender as address, rank() over (partition by sender order by block_id desc) as rank -- we only need the latest vote -- parsed_json:"com"/ pow(10,6) as comitted from algorand.transactions where tx_message:txn:rcv = '7+GeS/g87PiBmxw0xbZVWCAfjmrg/yo2KCFZEzqfGzA=' and note is not null -- make sure we can decode the base64 string and parsed_json is not null -- ignore invalid data and parsed_json:"com" is null and block_timestamp::date between '2022-01-31' and '2022-02-28' ), votes as ( select t.address as address, GET(parsed_json,0) as vote_id, GET(parsed_json,1) as vote_option from parsed_algo_txns t join algorand.account a on t.address = a.address where rank = 1 and vote_option not like '{%,%}' ), comitted_algo_txns as ( select sender, TRY_BASE64_DECODE_STRING(tx_message:txn:note) as note, SPLIT(note, ':j') as note_split, try_parse_json(GET(note_split,1)) as parsed_json, (parsed_json:"com") as comitted from algorand.payment_transaction where receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY' -- only want txs going to Governance Period #2 contract and note is not null -- make sure we can decode the base64 string and parsed_json is not null -- ignore invalid data and parsed_json:"com" is not null and is_integer(comitted) -- TX https://algoexplorer.io/tx/BMEYSYF6E2AJN57ELZKRHMURW2GX7WL36PVJ4EWLFF4Z5J3RWFSA and sender <> 'OJTHNRM6N3S4EPHVQOKIPWC6NAPXM2ZIYKMBK2TTCDCF6H2AQKM6OM2GIE' -- This wallet commited around 1.89 T on 28 Dec, but later withdrew and block_timestamp::date between '2021-12-24' and '2022-01-07' ), vote_and_commit as ( select v.vote_option, c.sender as address, c.comitted/1e6 as algo_amount, CASE WHEN algo_amount > 0 and algo_amount <= 1 THEN 'a.0-1' WHEN algo_amount > 1 and algo_amount <= 10 THEN 'b.1-10' WHEN algo_amount > 10 and algo_amount <= 50 THEN 'c.10-50' WHEN algo_amount > 50 and algo_amount <= 1e2 THEN 'd.50-100' WHEN algo_amount > 1e2 and algo_amount <= 1e3 THEN 'e.100-1000' WHEN algo_amount > 1e3 and algo_amount <= 1e4 THEN 'f.1K-10K' WHEN algo_amount > 1e4 and algo_amount <= 1e5 THEN 'g.10K-100K' WHEN algo_amount > 1e5 and algo_amount <= 1e6 THEN 'h.100K-1M' WHEN algo_amount > 1e6 and algo_amount <= 1e7 THEN 'i.1M-10M' WHEN algo_amount > 1e7 and algo_amount <= 1e8 THEN 'j.10M-100M' WHEN algo_amount > 1e8 THEN 'k.> 100M' END as category from votes v join comitted_algo_txns c on c.sender = v.address ) select category, vote_option, count(distinct(address)) from vote_and_commit group by category,vote_option order by category

34.Algo-Algorand Governance Period 2 Voting