SELECT

BLOCK_TIMESTAMP_HOUR::date AS Date,

BLOCK_COUNT,

TRANSACTION_COUNT,

TRANSACTION_COUNT_SUCCESS,

TRANSACTION_COUNT_FAILED,

TOTAL_FEES_NATIVE,

CASE

WHEN BLOCK_COUNT = 0 THEN 0

ELSE TRANSACTION_COUNT / 3600

END AS TPS,

CASE

WHEN TRANSACTION_COUNT = 0 THEN 0

ELSE (TRANSACTION_COUNT_SUCCESS * 100.0) / TRANSACTION_COUNT

END AS SUCCESS_RATE_PERCENT,

CASE

WHEN TRANSACTION_COUNT = 0 THEN 0

ELSE TOTAL_FEES_NATIVE / TRANSACTION_COUNT

END AS AVG_FEE_PER_TRANSACTION

FROM

sei.stats.ez_core_metrics_hourly

,together as (

SELECT

Date,

SUM(BLOCK_COUNT) AS BLOCK_COUNT,

SUM(TRANSACTION_COUNT) AS TOTAL_TRANSACTIONS,

SUM(TRANSACTION_COUNT_SUCCESS) AS TOTAL_SUCCESSFUL_TRANSACTIONS,

SUM(TRANSACTION_COUNT_FAILED) AS TOTAL_FAILED_TRANSACTIONS,

SUM(TOTAL_FEES_NATIVE) AS TOTAL_FEES_NATIVE,

AVG(TPS) AS AVG_TPS,

AVG(SUCCESS_RATE_PERCENT) AS AVG_SUCCESS_RATE,

AVG(AVG_FEE_PER_TRANSACTION) AS AVG_FEE_PER_TRANSACTION

FROM