SELECT

DATE_TRUNC('second', block_timestamp) AS seconds,

COUNT(*) AS cps

FROM aptos.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 1

AND VM_STATUS = 'Executed successfully'

GROUP BY 1

,

final as (SELECT

DATE_TRUNC('hour', seconds) AS hour,

avg(cps) AS avg_cps

FROM cps

GROUP BY 1

ORDER BY 1 DESC)

select avg(avg_cps) as "Avg CPS"

from final