Crazy_KidVote vs Non Vote Transactions Solana
    Updated 2022-02-12
    -- 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