select

date(hour) as p_date , symbol , avg(price) as prices

from optimism.core.fact_hourly_token_prices

where symbol in (select CURRENCY_SYMBOL from optimism.core.ez_nft_sales where PLATFORM_NAME = 'quixotic' )

and hour::date >= '2022-06-15'

group by 1,2

select

date(hour) as p_date , 'ETH' as symbol , avg(price) as prices

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH' and hour::date >= '2022-06-15'

group by 1,2

)

, price_usd as (

select block_timestamp::date as date, CURRENCY_SYMBOL , tx_hash, seller_address, buyer_address , tokenid , price , price*prices as price_usd,nft_address

from (select * from optimism.core.ez_nft_sales where PLATFORM_NAME = 'quixotic' ) a

left outer join price b on a.block_timestamp::date = b.p_date and symbol = CURRENCY_SYMBOL

select top 10

project_name, count(DISTINCT tx_hash) as total_sales , sum(price_usd) as volume

from price_usd a join optimism.core.dim_labels b on a.nft_address = b.address

group by 1

order by 2 desc