select

date_trunc('{{Interval}}', BLOCK_TIMESTAMP) AS DATE,

project_name,

count(DISTINCT tx_hash) as sale_count,

sum(price_usd) as volume,

avg(price_usd) as avg_volume,

count(distinct tokenid) nft_count,

count(distinct buyer_address) as buyers_count,

count(distinct seller_address) as seller_count

from optimism.core.ez_nft_sales a join crosschain.core.address_labels b on b.address = a.nft_address

WHERE BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - {{Since_Date}}

group by 1,2

)

select *,

(volume/sale_count) as vol_per_sales,

avg_volume*nft_count as marketcap,

sale_count/seller_count,

sale_count/buyers_count,

volume/seller_count,

volume/buyers_count,

RANK() OVER (PARTITION by date order by marketcap DESC) as rank,

sum(sale_count) over (partition by project_name order by date) as cum_sales_cnt,

sum(volume) over (partition by project_name order by date) as cum_volume,

sum(vol_per_sales) over (partition by project_name order by date) as cum_vol_per_sales,

sum(marketcap) over (partition by project_name order by date) as cum_marketcap

from tb

where sale_count is not null

qualify rank <= 10