hour::date as day,

avg(price) as price_token

from ethereum.core.fact_hourly_token_prices where SYMBOL = 'WETH'

group by 1),

tb1 as (select

PROJECT_NAME as NFT,

count(distinct tx_hash) as sale_no,

count(distinct BUYER_ADDRESS) as buyer_no,

sum(PRICE) as volume,

sum(PRICE*price_token) as volume_usd,

volume_usd/sale_no as average_price

from optimism.core.ez_nft_sales x join tb2 y on x.BLOCK_TIMESTAMP::date=y.day

left join optimism.core.dim_labels s on x.NFT_ADDRESS=s.ADDRESS

where PLATFORM_NAME='quixotic'

and EVENT_TYPE='sale'

and NFT is not null

group by 1 having volume_usd is not null

order by 2 desc limit 10)

PROJECT_NAME as NFT,

case

when price>0 and price<=0.01 then 'a. < 0.01 ETH'

when price>0.01 and price<=0.1 then 'b. 0.01-0.1 ETH'

when price>0.1 and price<=1 then 'c. 0.1-1 ETH'

when price>1 and price<=10 then 'd. 1-10 ETH'

when price>10 then 'e. > 10ETH'

end as bucket,

count(distinct tx_hash) as sale_no,

count(distinct BUYER_ADDRESS) as buyer_no,

sum(PRICE) as volume

from optimism.core.ez_nft_sales x

left join optimism.core.dim_labels s on x.NFT_ADDRESS=s.ADDRESS

where PLATFORM_NAME='quixotic'