LTirrellsol_tx_count-v-vote_ratio
Updated 2022-10-07
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
›
⌄
-- tx_count vs vote ratio
-- adapted from: https://app.flipsidecrypto.com/dashboard/NRSsGk
WITH votes AS (
SELECT
DATE_TRUNC(
'hour',
b.block_timestamp
) AS hour_,
tx_count,
num_votes,
num_votes / tx_count AS vote_ratio
FROM
solana.core.fact_blocks b
INNER JOIN solana.core.fact_votes_agg_block v ON (b.block_id = v.block_id)
WHERE
b.block_timestamp >= '2022-01-01'
ORDER BY
hour_ ASC
)
SELECT
tx_count,
avg(num_votes) as avg_votes,
avg(vote_ratio) as avg_vote_ratio,
min(vote_ratio) as min_vote_ratio,
max(vote_ratio) as max_vote_ratio
FROM
votes
GROUP BY
tx_count
ORDER BY
tx_count ASC;
Run a query to Download Data