keshanSolana votes per block
Updated 2022-02-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
›
⌄
⌄
/*with vote_txs as (
select block_id, max(block_timestamp::date) as day,
sum(num_votes) as votes
from solana.votes_block_agg
where block_timestamp::date >= '2022-2-1' and block_timestamp::date <= CURRENT_DATE::date - 1
group by block_id),
all_txs as (
select block_id, max(block_timestamp::date) as day,
sum(tx_count) as txs
from solana.blocks
where block_timestamp::date >= '2022-2-1' and block_timestamp::date <= CURRENT_DATE::date - 1
group by block_id)
select to_varchar(a.block_id) as block_id, a.day, txs, votes, iff((txs - votes) = 0, 0, (votes / (txs - votes))) as vote_to_non_vote_ratio
from all_txs a left join vote_txs v on a.block_id = v.block_id*/
with tx_minute as (
select date_trunc('minute', block_timestamp) as minute, count(block_id) as blocks, sum(tx_count) as txs
from solana.blocks
where block_timestamp::date >= '2022-2-1' and block_timestamp::date <= CURRENT_DATE::date - 1
group by minute
),
vote_minute as (
select date_trunc('minute', block_timestamp) as minute, count(block_id) as blocks, sum(num_votes) as votes
from solana.votes_block_agg
where block_timestamp::date >= '2022-2-1' and block_timestamp::date <= CURRENT_DATE::date - 1
group by minute
)
select a.minute, a.blocks as number_of_blocks, txs, votes, (votes / (txs - votes)) as vote_to_non_vote_ratio from tx_minute a left join vote_minute v on a.minute = v.minute
Run a query to Download Data