SELECT

COUNT(*) AS total_transactions

FROM

base.core.fact_transactions;

WHERE

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

AND BLOCK_TIMESTAMP < CURRENT_TIMESTAMP()

),

Total_Seconds_Last_30_Days AS (

SELECT

DATEDIFF(SECOND, DATEADD(DAY, -30, CURRENT_TIMESTAMP()), CURRENT_TIMESTAMP()) AS total_seconds

total_transactions,

total_seconds,

ROUND(total_transactions / NULLIF(total_seconds, 0), 6) AS tps -- Calculate TPS and round to 6 decimal places

FROM

Transactions_Last_30_Days,

Total_Seconds_Last_30_Days;