SELECT

NFT_Project_Name,

NFT_MCAP_USD,

NFTs_Sold,

(NFT_MCAP_USD / NFTs_Sold) as AVG_NFT_Price

FROM (

SELECT

m.project_name as NFT_Project_Name,

SUM(s.price_usd)::int as NFT_MCAP_USD,

COUNT(s.tx_hash) as NFTs_Sold

FROM ethereum.core.ez_nft_sales as s

INNER JOIN ethereum.core.ez_nft_mints as m

ON m.project_name = s.project_name

WHERE NFT_Project_Name IS NOT NULL

-- s.platform_name IS NOT NULL

AND s.block_timestamp:: DATE > '2022-12-31':: TIMESTAMP

--AND s.block_timestamp:: DATE > '2022-09-30':: TIMESTAMP

GROUP BY 1

HAVING SUM(s.price_usd) > 0

AND COUNT(s.tx_hash) > 0

ORDER BY NFT_MCAP_USD DESC

LIMIT 10

WHERE AVG_NFT_Price > 0

AND NFT_Project_Name IS NOT NULL

ORDER BY NFT_MCAP_USD DESC

LIMIT 10;