--SUCCESS FAIL
with failed as (select
BLOCK_TIMESTAMP::date as date ,
count (DISTINCT TX_HASH ) as success_transactions
from polygon.core.fact_transactions
where STATUS = 'SUCCESS'
and BLOCK_TIMESTAMP::date >='2022-07-01'
group by 1
),
all_ as (
select
BLOCK_TIMESTAMP::date as date ,
count (DISTINCT TX_HASH ) as num_transactions
from polygon.core.fact_transactions
where BLOCK_TIMESTAMP::date >='2022-07-01'
group by 1
)
select date ,
num_transactions,
(success_transactions/num_transactions)*100 as success_rate
from all_ join failed using (date)
group by 1,2 ,3