select

date_trunc('day', hour) as date,

symbol,

avg(price) as usd_price

from optimism.core.fact_hourly_token_prices

where

symbol in ('WETH', 'OP')

group by 1, 2

union all

select

date_trunc('day', hour) as date,

'ETH' as symbol,

avg(price) as usd_price

from optimism.core.fact_hourly_token_prices

where

symbol = 'WETH'

group by 1, 2

select

sum(price_usd) as "total sales volume (in usd)",

count(distinct buyer_address) as "total unique buyers",

count(distinct seller_address) as "total unique sellers",

count(distinct tx_hash) as "total sales count"

from optimism.core.ez_nft_sales join price n on block_timestamp::date = date and symbol = currency_symbol join optimism.core.dim_labels on address = nft_address

where

project_name = '{{collection}}' and

price_usd is not null