WITH n_ as (
SELECT
block_timestamp::date as date,
case
when BLOCK_NUMBER < 15537351 then 'Before Merg'
when BLOCK_NUMBER >= 15537351 then 'After Merg'
end as period,
count(*) as tx_count,
STATUS,
from_address,
count(CASE WHEN STATUS = 'FAIL' THEN 1 END) as fail_tx_count
FROM ethereum.core.fact_transactions
where block_timestamp::date >= '2022-09-01'
and block_timestamp::date <= '2022-09-29'
GROUP BY 1,2,4,5
)
SELECT
date,
period,
count(distinct from_address) as user_cnt,
sum(tx_count)/(86400) as tps,
sum(tx_count-fail_tx_count)/(1440) as tx_per_min,
sum(fail_tx_count)/sum(tx_count)*100 as fail_rate
FROM n_
group by 1,2
order by 1