Dune-Layer 2

 select 
date_trunc(‘day’,”block_time”) as “day”
, count(*)
from optimism.”transactions”
group by date_trunc(‘day’,”block_time”)
order by “day” asc
with first_tx as (
select min(“block_time”) as “min_block_time”
, “from”
from optimism.”transactions”
group by “from”),
users as (select date_trunc(‘day’,”min_block_time”) as “day”
, count(*) as users
from first_tx
group by date_trunc(‘day’,”min_block_time”))
select sum(users) over (order by “day”) as “cum_users”
, “day”
from users
select
avg((“gas_limit”*”gas_price”)/10¹⁸) as eth_gas_price
, date_trunc(‘day’,”block_time”) as “day”
from optimism.”transactions”
group by date_trunc(‘day’,”block_time”)
SELECT max(price) as price
, “day”
from dune_user_generated.”uniswap_prices”
where “symbol”=’ETH’
group by “day”
with mints as (
select bytea2numeric(“data”)/1e18 as “eth”
, date_trunc(‘day’,”block_time”) as “day”
from optimism.”logs”
where “contract_address”= ‘\x4200000000000000000000000000000000000006’ — ETH
and
“topic2”= ‘\x0000000000000000000000000000000000000000000000000000000000000000’
)
,
burns as (
select -1 * bytea2numeric(“data”)/1e18 as “eth”
, date_trunc(‘day’,”block_time”) as “day”
from optimism.”logs”
where “contract_address”= ‘\x4200000000000000000000000000000000000006’ — ETH
and
“topic3”= ‘\x0000000000000000000000000000000000000000000000000000000000000000’
select “_prevTotalElements”
from optimism.”OVM_CanonicalTransactionChain_evt_TransactionBatchAppended”
order by evt_block_time desc
limit 1
select max(“_prevTotalElements”) as “cum_elements”
, date_trunc(‘day’,”evt_block_time”) as “day”
from optimism.”OVM_CanonicalTransactionChain_evt_TransactionBatchAppended”
group by date_trunc(‘day’,”evt_block_time”)
order by date_trunc(‘day’,”evt_block_time”) asc
with batches as (select date_trunc(‘day’,”evt_block_time”) as “day”
,
“evt_block_time” — lag(“evt_block_time”) over (order by “evt_block_time” asc) as “batch_time”
from optimism.”OVM_CanonicalTransactionChain_evt_TransactionBatchAppended”)
select “day”
, avg(“batch_time”) as “avg_batch_time”
from batches
group by “day”
order by “day” asc

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store