WITH daily_totals AS (

SELECT

COUNT(tx_from) AS daily_wallet_addresses,

COUNT(DISTINCT tx_from) AS daily_accounts,

COUNT(tx_id) AS daily_transactions,

SUM(CASE WHEN tx_succeeded = TRUE THEN 1 ELSE 0 END) AS daily_successful,

SUM(CASE WHEN tx_succeeded = FALSE THEN 1 ELSE 0 END) AS daily_failed,

SUM(fee/ pow(10, 6)) AS daily_gas_fees,

SUM(gas_used) AS daily_gas_used,

SUM(gas_wanted) AS daily_gas_wanted

FROM

lava.core.fact_transactions

WHERE

block_timestamp >= '2024-07-01' AND block_timestamp < '2024-08-01'

GROUP BY

date_trunc('day', block_timestamp)

AVG(daily_wallet_addresses) AS avg_wallet_addresses_per_day,

AVG(daily_accounts) AS avg_accounts_per_day,

AVG(daily_transactions) AS avg_transactions_per_day,

AVG(daily_successful) AS avg_successful_per_day,

AVG(daily_failed) AS avg_failed_per_day,

AVG(daily_gas_fees) AS avg_gas_fees_per_day,

AVG(daily_gas_used) AS avg_gas_used_per_day,

AVG(daily_gas_wanted) AS avg_gas_wanted_per_day

daily_totals;