with info as (
select
trunc(block_timestamp,'hour') as hour,
count(distinct tx_hash) as txs,
txs/3600 as tps
from near.core.fact_transactions
group by 1
order by 1 asc
),
final_tps as (
SELECT
trunc(hour,'day') as date,
avg(tps) as tps
from info
group by 1
order by 1 asc
),
avg_info as (
select avg(tps) as avg_tps
from final_tps
)
select date as "Day", tps as "TPS", avg_tps as "Average TPS"
from final_tps, avg_info