Arbitrum

with
-------------------- Prices --------------------
  prices as (
    select
      timestamp,
      price as eth_price
    from
      prices.minute
    where
      blockchain = 'arbitrum'
      and contract_address  = 0x82af49447d8a07e3bd95bd0d56f35241523fbab1
  ),
-------------------- Transactions --------------------
  transactions as (
    select
      block_time,
      block_number,
      block_hash,
      hash as tx_hash,
      success,
      type,
      timeboosted,
      "from" as from_address,
      "to" as to_address,
      index as tx_index,
      nonce,
      value,
      gas_limit,
      gas_used,
      gas_used / nullif(gas_limit, 0) as gas_target,
      gas_used_for_l1 as gas_used_l1,
      gas_used - gas_used_for_l1 as gas_used_l2,
      gas_price as gas_price_bid_wei,
      priority_fee_per_gas as priority_fee_per_gas_wei,
      max_fee_per_gas as max_fee_per_gas_wei,
      max_priority_fee_per_gas as max_priority_fee_per_gas_wei,
      effective_gas_price as gas_price_paid_wei,
      case
        when block_number >= 419260699 then 20000000 -- ArbOS 51 Dia
        when block_number >= 191918583 and block_number < 419260699 then 10000000
        else 100000000
      end as base_fee_per_gas_wei,
      access_list,
      authorization_list,
      data
    from
      arbitrum.transactions
  ),
-------------------- Gas Fees --------------------
  fees as (
    select
      t.block_time,
      t.block_number,
      t.block_hash,
      t.tx_hash,
      t.success,
      t.type,
      t.timeboosted,
      t.from_address,
      t.to_address,
      t.tx_index,
      t.nonce,
      t.value,

      -- Gas Usage
      t.gas_limit,
      t.gas_used,
      t.gas_target,
      t.gas_used_l1,
      t.gas_used_l2,

      -- Gas Params: Chain
      t.base_fee_per_gas_wei,
      t.max_fee_per_gas_wei,
      t.max_priority_fee_per_gas_wei,

      -- Gas Params: User
      t.gas_price_bid_wei, -- Redundant
      t.priority_fee_per_gas_wei, -- Redundant
      t.gas_price_paid_wei,

      -- Gas Fees
      t.gas_used_l1 * t.gas_price_paid_wei as l1_fee_wei,
      t.gas_used_l2 * t.gas_price_paid_wei as l2_fee_wei,
      t.gas_used_l2 * t.base_fee_per_gas_wei as l2_base_fee_wei,
      t.gas_used_l2 * (t.gas_price_paid_wei - t.base_fee_per_gas_wei) as l2_surplus_fee_wei,
      
      t.gas_used * t.gas_price_paid_wei as tx_fee_wei,
      t.gas_used * t.gas_price_paid_wei / 1e18 * p.eth_price as tx_fee_usd,
      
      t.access_list,
      t.authorization_list,
      t.data
    from
      transactions as t
      -- join blocks as b on t.block_number = b.block_number
      join prices as p on date_trunc('minute', t.block_time) = p.timestamp
  )
-------------------- Output --------------------
select
  date_trunc('hour', block_time) as date,
  
  avg(gas_used) as gas_used_avg,
  avg(gas_used_l1) as gas_used_l1_avg,
  avg(gas_used_l2) as gas_used_l2_avg,
  avg(base_fee_per_gas_wei) / 1e9 as base_fee_gwei_avg,
  avg(gas_price_paid_wei) / 1e9 as gas_price_paid_gwei_avg,

  avg(l1_fee_wei) / 1e9 as l1_fee_gwei_avg,
  avg(l2_fee_wei) / 1e9 as l2_fee_gwei_avg,
  avg(l2_base_fee_wei) / 1e9 as l2_base_fee_gwei_avg,
  avg(l2_surplus_fee_wei) / 1e9 as l2_surplus_fee_gwei_avg,
  avg(tx_fee_wei) / 1e9 as tx_fee_gwei_avg,
  avg(tx_fee_usd) as tx_fee_usd_avg
from
  fees
group by
  1
order by
  date desc