SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,

COUNT(DISTINCT TX_HASH) AS daily_tx_count,

DATEDIFF('second', MIN(BLOCK_TIMESTAMP), MAX(BLOCK_TIMESTAMP)) AS seconds_in_day

FROM boba.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= DATEADD('year', -1, CURRENT_DATE)

GROUP BY 1

SELECT

day,

daily_tx_count,

ROUND(daily_tx_count / NULLIF(seconds_in_day, 0), 4) AS "Daily AVG TPS"

FROM daily_tx

ORDER BY day;