COUNT (DISTINCT TX_ID) AS TXN,

COUNT (DISTINCT PURCHASER) AS BUYERS,

COUNT (DISTINCT SELLER) AS SELLERS,

COUNT (DISTINCT s.MINT) AS NFT_IDs,

SUM (SALES_AMOUNT * price) AS USD,

Median (SALES_AMOUNT * price) AS avg_USD,

CONTRACT_NAME as Project

from solana.core.fact_nft_sales s

left join (select date_trunc ('day',RECORDED_HOUR) as TIME, median (CLOSE) as price from solana.core.fact_token_prices_hourly WHERE SYMBOL = 'SOL' GROUP by 1) on block_timestamp::DATE = TIME

LEFT JOIN solana.core.dim_nft_metadata m on s.MINT=m.MINT

WHERE BLOCK_TIMESTAMP >= '2021-01-01'

AND CONTRACT_NAME is not null

AND CONTRACT_NAME NOT in ('opensea','rarible nfts')

GROUP BY Project

ORDER BY TXN DESC LIMIT 10