WITH daily_stats AS (

SELECT

DATE_TRUNC('day', block_timestamp) as date,

COUNT(DISTINCT block_number) as blocks_created,

COUNT(DISTINCT tx_hash) as total_transactions,

AVG(gas_used) as avg_gas_used,

AVG(tx_fee) as avg_tx_fee

FROM mezo.testnet.fact_transactions

WHERE block_timestamp >= (

SELECT MIN(block_timestamp)

FROM mezo.testnet.fact_transactions

)

GROUP BY 1

ORDER BY 1

SELECT

date as "day",

blocks_created as "no. of blocks created",

total_transactions as "no. of tx per day",

ROUND(avg_tx_fee, 6) as "avg tx fee",

ROUND(avg_gas_used, 2) as "avg gas used",

FROM daily_stats;