elvisArb Vs BSC aggregate stats (Block Size)
    Updated 2023-03-14
    WITH arb_block_tx_stats as (
    SELECT block_hash,
    sum(tx_fee) as block_fees,
    count(CASE WHEN status != 'SUCCESS' THEN tx_hash END) as failed_tx_count,
    sum(eth_value) block_eth_value,
    avg(CASE WHEN status = 'SUCCESS' AND eth_value > 0 THEN tx_fee/eth_value END) as eth_trf_cost_ratio
    FROM arbitrum.core.fact_transactions
    WHERE block_timestamp > {{start_date}}
    AND block_timestamp < {{end_date}}
    GROUP BY block_hash
    ),
    arb_period_stats as (
    SELECT A.network, any_value(A.blockchain) as blockchain,

    AVG(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as "Average block time",
    median(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as "Median block time",
    min(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as "Minimum block time",
    max(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as "Maximum block time",
    STDDEV(timestampdiff(second, P.block_timestamp, A.block_timestamp)) as "STDDEV block time",

    AVG(A.difficulty) as "Average difficulty",
    median(A.difficulty) as "Median difficulty",
    min(A.difficulty) as "Minimum difficulty",
    max(A.difficulty) as "Maximum difficulty",
    STDDEV(A.difficulty) as "STDDEV difficulty",

    AVG(A.size) as "Average Block size",
    median(A.size) as "Median Block size",
    min(A.size) as "Minimum Block size",
    max(A.size) as "Maximum Block size",
    STDDEV(A.size) as "STDDEV Block size",

    AVG(A.gas_used) as "Average Gas Used per block",
    median(A.gas_used) as "Median Gas Used per block",
    min(A.gas_used) as "Minimum Gas Used per block",
    max(A.gas_used) as "Maximum Gas Used per block",