AS (

SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day

,SUM(TX_COUNT) AS total_tx_count

,MAX(BLOCK_TIMESTAMP) AS max_block_timestamp

,MIN(BLOCK_TIMESTAMP) AS min_block_timestamp

FROM m1.core.fact_blocks

GROUP BY DATE_TRUNC('day', BLOCK_TIMESTAMP)

)

SELECT day

,total_tx_count / NULLIF(DATEDIFF(second, min_block_timestamp, max_block_timestamp), 0) AS daily_avg_tps

FROM block_data

ORDER BY day DESC;