SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS transaction_day,

COUNT(*) AS total_transactions

FROM

base.core.fact_transactions;

WHERE

BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())

AND BLOCK_TIMESTAMP < CURRENT_TIMESTAMP()

GROUP BY

transaction_day

),

Daily_TPS AS (

SELECT

transaction_day,

total_transactions,

86400 AS seconds_in_day,

ROUND(total_transactions / 86400, 6) AS tps

FROM

Daily_Transactions

transaction_day,

total_transactions,

tps

FROM

Daily_TPS

ORDER BY

transaction_day;