h4wk(%) STPM Comparison
    Updated 2024-07-06
    -- FLOW
    with flow_total_avg_for_minute as (
    select
    count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min
    from flow.core.fact_transactions
    where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01'
    )

    , flow_success_per_min as (select
    count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min
    from flow.core.fact_transactions
    where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01'
    and tx_succeeded = True
    )
    , base_flow as (
    select * from (
    select tx_per_min, 'Total' as type from (select * from flow_total_avg_for_minute)
    UNION
    select tx_per_min, 'Success' as type from (select * from flow_success_per_min)
    ) order by type
    )
    , final_flow as (
    select 'Flow' as chain,
    round(sum(case when type = 'Total' then tx_per_min end), 2) as tpm,
    round(sum(case when type = 'Success' then tx_per_min end), 2) as stpm,
    stpm/tpm*100 as percentage
    from base_flow
    )

    -- ETH
    , eth_total_avg_for_minute as (
    select
    count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min
    from ethereum.core.fact_transactions
    where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01'
    )