SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS date,

COUNT(TX_HASH) AS total_transactions,

SUM(CASE WHEN TX_SUCCEEDED = 'TRUE' THEN 1 ELSE 0 END) AS successful_transactions,

CASE

WHEN COUNT(TX_HASH) > 0 THEN

SUM(CASE WHEN TX_SUCCEEDED = 'TRUE' THEN 1 ELSE 0 END) / COUNT(TX_HASH)

ELSE 0

END AS success_rate,

SUM(tx_fee) AS fees,

AVG(tx_fee) AS avg_tx_fee,

COUNT(DISTINCT FROM_ADDRESS) AS unique_users,

AVG(DATEDIFF(MINUTE, INSERTED_TIMESTAMP, BLOCK_TIMESTAMP)) AS latency_MINUTES

FROM

flow.core_evm.fact_transactions

WHERE

--BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE)

BLOCK_TIMESTAMP < TRUNC(CURRENT_DATE,'WEEK')

GROUP BY

1

SELECT

e.date,

e.total_transactions AS evm_transactions,

e.successful_transactions AS evm_successful_transactions,

e.success_rate AS evm_success_rate,

e.fees AS evm_total_fees,

e.avg_tx_fee AS evm_avg_tx_fee,

e.unique_users AS evm_unique_users,

e.latency_MINUTES AS evm_latency

FROM

evm_data e

ORDER BY

e.date DESC