PRICE as ( select

hour

,price as ETH_Price

from optimism.core.fact_hourly_token_prices

where symbol = 'ETH' )

,

maintable as (

select block_timestamp,tx_hash,

buyer_address,

seller_address,

price_usd,

price_usd/ETH_Price as ETH_Volume

from optimism.core.ez_nft_sales a join PRICE p on date_trunc('hour',a.block_timestamp)= p.hour

where a.block_timestamp:: date < CURRENT_DATE and a.block_timestamp:: date>= '2021-12-18' and PRICE_USD is not null )

,

maintable2 as (

select block_timestamp,tx_hash,

buyer_address,

seller_address,

price_usd

,price_usd/ETH_Price as ETH_Volume

from ethereum.core.ez_nft_sales a join PRICE p on date_trunc('hour',a.block_timestamp)= p.hour

where a.block_timestamp:: date < CURRENT_DATE and a.block_timestamp:: date>='2021-12-18' and PRICE_USD is not null)

(select 'Ethereum' as Blockchain, block_timestamp::date,

Avg(ETH_Volume) as "Avg price"

from maintable2 where ETH_Volume is not null

group by 1,2

order by 2 asc

)

union all

(select 'Optimism' as Blockchain, block_timestamp::date,