--Show the average TPS per second by hour
--Show transaction gas price over time
select date(block_timestamp) as date,
hour(block_timestamp) as hour,
count(*)/3600 as TPS,
avg(gas_price) as avg_gas_price, --> to properly measur it in AVAX this has to be divided by 1000000000
avg(tx_fee) as avg_tx_fee,
avg(avax_value) as avg_avax_value
from avalanche.core.fact_transactions
where date > '2022-06-19'
group by date, hour
--Note any other interesting findings about the number of transactions, blocks, or gas for the Avalanche blockchain!
--For this item we'll show correlation between avg_gas_price, avg_tx_fee, avg_avax_value and TPS