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

),

volume as (

select

date_trunc('day', block_timestamp) as date,

nft_address,

sum(price * eth) as volume_in_usd

from optimism.core.ez_nft_sales join eth on block_timestamp::date = day

where

date < current_date and

currency_symbol = 'ETH' and

platform_address in ('0x20975da6eb930d592b9d78f451a9156db5e4c77b', '0x065e8a87b8f11aed6facf9447abe5e8c5d7502b6', '0x3f9da045b0f77d707ea4061110339c4ea8ecfa70') and

event_type = 'sale'

group by 1, 2

union all

select