SELECT
date_trunc('DAY', v.block_timestamp) as FECHA,
count(v.block_id) as Blocks_per_day,
sum(v.num_votes) as Total_Vote_tx_per_day,
sum(b.tx_count) as Total_tx_per_day,
Total_tx_per_day-Total_Vote_tx_per_day as Total_Non_vote_tx_per_day,
Total_Vote_tx_per_day/Blocks_per_day as AVG_vote_tx_per_block,
Total_Non_vote_tx_per_day/Blocks_per_day as AVG_non_vote_tx_per_block,
Total_tx_per_day/Blocks_per_day as AVG_tx_per_block
FROM
solana.votes_block_agg v
INNER JOIN solana.blocks b
ON v.block_id=b.block_id
WHERE
v.block_timestamp >= '2022-02-01'
GROUP BY 1
ORDER BY 1