Crazy_KidAlgorand Governance Period 2 Voting 2
Updated 2022-03-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
›
⌄
WITH commitment AS (SELECT DATE(block_timestamp) as date, sender as committer, tx_id,
SUBSTRING(try_base64_decode_string(tx_message:txn:note), 17, len(try_base64_decode_string(tx_message:txn:note))-17) as
commitment_amount
FROM algorand.payment_transaction
WHERE date(block_timestamp) BETWEEN '2021-12-24' AND '2022-01-07'
AND try_base64_decode_string(tx_message:txn:note) like 'af/gov1:j{"com%'
AND receiver ='57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'
GROUP BY date, committer, tx_id, commitment_amount
ORDER BY date),
recent_commitment AS (SELECT * FROM ( SELECT *, row_number() over (partition BY committer order BY date DESC) r FROM commitment ) T
WHERE T.r=1
ORDER BY date),
vote AS (SELECT DATE(block_timestamp) as date, sender as voter, tx_id,
SUBSTRING(try_base64_decode_string(tx_message:txn:note::string), 14, 1) as vote_option
--Syntax for SUBSTRING() function:SUBSTRING(expression, starting_position, length)
FROM algorand.payment_transaction
WHERE date(block_timestamp) BETWEEN '2022-01-31' AND '2022-02-28'
AND try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:j%' AND vote_option IN ('a', 'b')
AND receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'
GROUP BY date, voter, tx_id, vote_option
ORDER BY date),
recent_votes AS (SELECT * FROM ( SELECT *, row_number() over (partition BY voter order BY date DESC) r FROM vote ) T
WHERE T.r=1
ORDER BY date)
SELECT vote_option, COUNT(voter) as votes, sum(commitment_amount)/1000000 as committed_amount
FROM recent_commitment JOIN recent_votes ON committer = voter
WHERE commitment_amount not LIKE '%bnf%' AND commitment_amount not LIKE '%}%' AND commitment_amount not LIKE '%:%'
GROUP BY vote_option
ORDER BY vote_option
Run a query to Download Data