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