from solana.core.fact_transactions where BLOCK_TIMESTAMP::date >='2022-06-01' and BLOCK_TIMESTAMP::date <='2022-06-30'

group by 1 order by 1 asc),

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

from solana.core.fact_transactions where BLOCK_TIMESTAMP::date >='2022-06-01' and BLOCK_TIMESTAMP::date <='2022-06-30' and SUCCEEDED='1'

group by 1 order by 1 asc),

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

from solana.core.fact_transactions where BLOCK_TIMESTAMP::date >='2022-06-01' and BLOCK_TIMESTAMP::date <='2022-06-30' and 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