SELECT

date_trunc(day,HOUR) block_timestamp_,

avg(price) ethprice

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol in ('WETH')

AND date_trunc(day,HOUR) >= '2022-07-01' and date_trunc(day,HOUR)<'2022-08-01'

group by 1

select BLOCK_TIMESTAMP::date date ,

sum (TX_FEE) FEE ,

sum (FEE) over (order by date ) as cum_fee ,

sum (TX_FEE * ethprice) FEE_USD,

sum (FEE_USD) over (order by date ) as cum_FEE_USD

from optimism.core.fact_transactions join eth_price on block_timestamp_::date = BLOCK_TIMESTAMP::date

where BLOCK_TIMESTAMP::date >= '2022-07-01' and BLOCK_TIMESTAMP::date <'2022-08-01'

group by 1