MostlyData_gas vs position
    Updated 2024-11-08
    -- forked from PF by gas used @ https://flipsidecrypto.xyz/studio/queries/f88e20d4-d529-4e2d-806d-84d6f4dee28d

    -- forked from PF by cumulative gas @ https://flipsidecrypto.xyz/studio/queries/33f79e4d-b607-47ea-a0cf-67a299715b59

    -- forked from PF by position @ https://flipsidecrypto.xyz/studio/queries/88bff19e-c35e-416d-9b87-7a4841aae922

    with block_info as(
    select
    block_number
    ,tx_count
    ,gas_used
    ,block_header_json['baseFeePerGas'] as baseFeePerGas

    from ethereum.core.fact_blocks

    where
    block_timestamp >= current_date() - interval '{{n_days}} days'
    and tx_count < '{{filter_block_size}}'
    )

    ,tx_data as(
    select
    txs.position
    ,txs.gas_used
    from ethereum.core.fact_transactions txs
    inner join block_info bi on bi.block_number = txs.block_number
    where
    block_timestamp >= current_date() - interval '{{n_days}} days'
    )


    select
    floor(position / 10) * 10 as position_group,
    percentile_cont(0.5) within group (order by gas_used) as median,
    QueryRunArchived: QueryRun has been archived