Crazy_KidVote vs Non Vote Transactions Solana
Updated 2022-02-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
-- Create a visualization that compares the number of non-vote transactions to vote transactions per block since the start
-- of February (hint, use the block table and the votes_block_agg table). What is the average ratio of votes to non-votes in a block?
-- Have there been any timeframes of noticeable fluctuations of this ratio?
WITH total AS (SELECT DATE(block_timestamp) as date, round(sum(tx_count)/COUNT(DISTINCT block_id)) as tx_per_block
FROM solana.blocks
WHERE date >= '2022-02-01'
GROUP BY date
ORDER BY date),
vote AS (SELECT DATE(block_timestamp) as date, round(sum(num_votes)/COUNT(DISTINCT block_id)) as vote_tx_per_block
FROM solana.votes_block_agg
WHERE date >= '2022-02-01'
GROUP BY date
ORDER BY date)
SELECT total.date as date, tx_per_block, vote_tx_per_block, tx_per_block - vote_tx_per_block as non_vote_tx_per_block,
vote_tx_per_block/non_vote_tx_per_block as "ratio = no of vote tx per block/no of non vote tx per block"
FROM total JOIN vote on total.date = vote.date
ORDER BY date
-- avg of blocks per day is considered
Run a query to Download Data