date_trunc('hour', block_timestamp) as mdate,

count(case when STATUS = 'SUCCESS' then TRUE else NULL end ) as success,

count(DISTINCT TX_HASH) as tx_count,

tx_count - success as fails,

success / (tx_count) as success_rate,

1 - success_rate as failure_rate

from gnosis.core.fact_transactions

where date(block_timestamp) >= '2022-09-01'

group by mdate

select date_trunc('day', mdate) as daily, avg(tx_count) as avg_tpm, avg(success_rate) as avg_success_rate, avg(failure_rate) as avg_failure_rate, sum(tx_count) as total_trxs

from alltx

group by daily