binhachon4. Governance Period 1 Breakdown - Vote with amount - Percentile
Updated 2022-01-08
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
34
35
36
›
⌄
with vote as (
select
block_id,
sender,
base64_decode_string(tx_message:txn:note::string) as note,
substr(note, position('[', note, 1)) as vote,
substr(vote, position(',', vote, 1) + 2, 1) as selection,
row_number() over (partition by sender order by block_id desc) as rownumber
from algorand.payment_transaction
where receiver = 'GULDQIEZ2CUPBSHKXRWUW7X3LCYL44AI5GGSHHOQDGKJAZ2OANZJ43S72U'
and amount is null
and note like '%af/gov1:j[3,%'
qualify rownumber = 1
),
gov_period_1 as (
select
block_id,
sender,
base64_decode_string(tx_message:txn:note::string) as note,
case when position('"com": ', note, 1) != 0 then 8
when position('"com": ', note, 1) != 0 then 7
else 6 end as pos,
position('"com":', note, 1) as len1,
position('}', note, 1) as len2,
substr(note, position('"com":', note, 1) + pos, len(note) - position('"com":', note, 1) - pos)::float/1e6 as amount,
row_number() over (partition by sender order by block_id desc) as rownumber
from algorand.payment_transaction
where receiver = 'GULDQIEZ2CUPBSHKXRWUW7X3LCYL44AI5GGSHHOQDGKJAZ2OANZJ43S72U'
and amount is null
and note like '%af/gov1:j{"com":%'
and note not like '%af/gov1:j{"com":{%'
and len(note) = position('}', note,1)
qualify rownumber = 1
),
vote_with_amount as (
select
Run a query to Download Data