strawbettywallet votes over time
Updated 2022-03-21
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
›
⌄
with all_votes as (
select
sender,
block_id,
block_timestamp::date as date,
try_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
from algorand.payment_transaction
where amount is null
and date >= '2022-01-31' and date <= '2022-02-28'
and note like 'af/gov1:j%'
), last_votes as (
SELECT DISTINCT(all_votes.sender) as sender, selection, date, block_id
FROM (
SELECT sender, max(block_id) as last_block
FROM all_votes
GROUP BY sender) as last_vote
INNER JOIN all_votes
ON all_votes.sender = last_vote.sender AND all_votes.block_id = last_vote.last_block
WHERE selection IN ('a', 'b')
)
select date, selection, count(*) from last_votes
group by 1, 2
order by date
Run a query to Download Data