with eth as (

select

hour::date as day,

avg(price) as eth

from ethereum.core.fact_hourly_token_prices

where

day between '2022-06-01' and current_date - 1 and

symbol = 'WETH'

group by 1

),

op as (

select

hour::date as day,

avg(price) as op

from optimism.core.fact_hourly_token_prices

where

day between '2022-06-01' and current_date - 1 and

symbol = 'OP'

group by 1

select

date_trunc('day', block_timestamp) as date,

sum(price) as volume_in_token,

sum(volume_in_token) over (order by date) as cumulative_volume_in_token,

avg(eth) as price,

sum(price * eth) as volume_in_usd,

sum(volume_in_usd) over (order by date) as cumulative_volume_in_usd,

count(distinct tx_hash) as sales_count,

sum(sales_count) over (order by date) as cumulative_sales_count,

count(distinct seller_address) as unique_seller,

sum(unique_seller) over (order by date) as cumulative_unique_seller,

count(distinct buyer_address) as unique_buyer,

sum(unique_buyer) over (order by date) as cumulative_unique_buyer,

'ETH' as currency