SELECT date_trunc('hour', date) as "date",

round(avg(T_P_S),2) as "TPS"

FROM (

SELECT

date,

num_of_txns,

num_of_txns /DATEDIFF(second,min_block_timestamp,max_block_timestamp)as T_P_S

FROM (

SELECT

date_trunc('hour', block_timestamp) as date,

count(DISTINCT tx_hash) as num_of_txns,

min(block_timestamp) as min_block_timestamp,

max(block_timestamp) as max_block_timestamp

FROM blast.core.fact_transactions

GROUP BY 1

)

GROUP BY 1

ORDER by 1 desc