SELECT

date,

num_of_txns,

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

((num_of_txns - LAG(num_of_txns)over(ORDER BY date))/LAG(num_of_txns)over(ORDER BY date))*100 as rate_of_growth_txns

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 kaia.core.fact_transactions

GROUP BY 1

ORDER BY date DESC