nickpayiatis_2022-12-15 03:33 PM
Updated 2022-12-16
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
›
⌄
with raw_votes as(
select tx_sender, block_timestamp,
try_base64_decode_string(tx_message:txn:note::string)as votes ,
substr(votes, 14, 1) as measure_1,
substr(votes, 18, 1) as measure_2,
substr(votes, 22, 1) as measure_3,
substr(votes, 26, 1) as measure_4,
substr(votes, 30, 1) as measure_5
from algorand.core.ez_transaction_payment
where
try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:%'
and block_timestamp::date between '2022-12-05' and '2022-12-18'
)
,max_vote as(
select tx_sender, max(block_timestamp) as block_timestamp
from algorand.core.ez_transaction_payment
where
try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:%'
and block_timestamp::date between '2022-12-05' and '2022-12-18'
group by tx_sender
)
select rv.tx_sender, l.*, rv.block_timestamp, rv.votes
from raw_votes rv
join max_vote mv on rv.tx_sender = mv.tx_sender and rv.block_timestamp = mv.block_timestamp
join algorand.core.dim_label l on rv.tx_sender = l.address
and label_type = 'cex'
Run a query to Download Data