adriaparcerisasPolygon TPB comparison
    Updated 2022-07-08
    -- What's the average number of transactions per block? What's the max number of transactions we've seen in a block and the minimum?-
    -- Show the average time between blocks over time
    -- Show transaction gas price over time.
    -- Note any other interesting findings about the number of transactions, blocks, or gas for the Polygon blockchain!
    WITH
    poly_tpb as (
    select
    block_number,
    block_timestamp,
    count(distinct tx_hash) as txs
    from polygon.core.fact_transactions
    where block_timestamp> CURRENT_DATE-INTERVAL '1 MONTH' and status='SUCCESS'
    group by 1,2
    ),
    poly_final_tpb as (
    SELECT
    trunc(block_timestamp,'day') as date,
    avg(txs) as avg_tpb
    from poly_tpb
    group by 1
    order by 1 asc
    ),
    avax_tpb as (
    select
    block_number,
    block_timestamp,
    count(distinct tx_hash) as txs
    from avalanche.core.fact_transactions
    where block_timestamp> CURRENT_DATE-INTERVAL '1 MONTH' and status='SUCCESS'
    group by 1,2
    ),
    avax_final_tpb as (
    SELECT
    trunc(block_timestamp,'day') as date,
    avg(txs) as avg_tpb
    from avax_tpb
    Run a query to Download Data