from flow.core.fact_events where BLOCK_TIMESTAMP::date >='2022-05-09' and BLOCK_TIMESTAMP::date <='2022-06-04'

group by 1 order by 1 asc),

s as(select date_trunc('MINUTE',BLOCK_TIMESTAMP ) as min, count(distinct TX_ID) as num_success

from flow.core.fact_events where BLOCK_TIMESTAMP::date >='2022-05-09' and BLOCK_TIMESTAMP::date <='2022-06-04' and TX_SUCCEEDED='1'

group by 1 order by 1 asc),

f as (select date_trunc('MINUTE',BLOCK_TIMESTAMP ) as min, count(distinct TX_ID) as num_failed

from flow.core.fact_events where BLOCK_TIMESTAMP::date >='2022-05-09' and BLOCK_TIMESTAMP::date <='2022-06-04' and TX_SUCCEEDED='0'

group by 1

order by 1 asc)

select date_trunc('day',t.min) as day, Avg(num_total) as Avg_All, Avg(num_success) as Avg_suc, Avg(num_failed) as Avg_fail, sum(num_success)/sum(num_total)*100 as sussess_rate

from t left join s on t.min=s.min left join f on t.min=f.min

group by 1

order by 1 asc