MasiDaily Success Rate
    Updated 2024-11-16
    with tb1 as (select block_timestamp_hour,
    BLOCKCHAIN,
    (TRANSACTION_COUNT_SUCCESS / TRANSACTION_COUNT) * 100 AS SuccessRates
    from crosschain.stats.ez_core_metrics_hourly
    where block_timestamp_hour::date >= '2024-10-01' and block_timestamp_hour::date < '2024-11-01'
    and blockchain in ('polygon','near','sei','blast','arbitrum','avalanche','axelar','bsc','optimism','aptos','ethereum','base','solana','flow')
    )
    ,
    tb2 as ( select trunc(block_timestamp,'hour') as block_timestamp_hour ,
    count(DISTINCT tx_hash) as TRANSACTION_COUNT_SUCCESS
    from kaia.core.fact_transactions
    where block_timestamp::date >= '2024-10-01' and block_timestamp::date < '2024-11-01'
    and tx_succeeded = 'TRUE'
    group by 1)
    ,
    tb3 as ( select trunc(block_timestamp,'hour') as block_timestamp_hour ,
    count(DISTINCT tx_hash) as TRANSACTION_COUNT
    from kaia.core.fact_transactions
    where block_timestamp::date >= '2024-10-01' and block_timestamp::date < '2024-11-01'
    group by 1)
    ,
    tb4 as (
    select block_timestamp_hour,
    blockchain,
    SuccessRates
    from tb1
    UNION all
    select a.block_timestamp_hour,
    'kaia' as blockchain,
    (TRANSACTION_COUNT_SUCCESS / TRANSACTION_COUNT) * 100 AS SuccessRates
    from tb2 a join tb3 b on a.block_timestamp_hour::date = b.block_timestamp_hour::date
    having SuccessRates <= 100
    )

    select trunc(block_timestamp_hour,'day') as daily,
    blockchain,