select

date_trunc('day', hour) as date,

avg(price) as price

from

ethereum.core.fact_hourly_token_prices

where

symbol = 'WETH'

and hour >= CURRENT_DATE - 90

group by

date

),

txns as (

select

t.*,

(

(t.gas_price / 1e9) * (t.gas_used)

) * (price) as gas_used_usd

from

optimism.core.fact_transactions t

join eth_price p on t.block_timestamp :: date = p.date

)

select

block_timestamp :: date as date,

status as type,

count(distinct tx_hash) as tx_frequncy,

sum(tx_frequncy) over (

order by

date asc rows between unbounded preceding

and current row

) as cum_tx_frequncy

from

txns

group by

date,

type