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

FROM (

SELECT

date,

num_of_txns,

CASE when DATEDIFF(second,min_block_timestamp,max_block_timestamp) = '0' then NULL else num_of_txns/DATEDIFF(second,min_block_timestamp,max_block_timestamp) end as T_P_S

FROM (

SELECT

date_trunc('day', 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 aptos.core.fact_transactions

GROUP BY 1

WHERE date <= current_date - 1

AND date > '2020-12-29 00:00:00.000'

)

GROUP BY 1

ORDER BY 1 DESC