concat ('Ethereum: ',PROJECT_NAME) as Project,

date_trunc ('week',BLOCK_TIMESTAMP) as date,

count (DISTINCT TX_HASH ) as TXN,

count (DISTINCT BUYER_ADDRESS ) as BUYER,

count (DISTINCT SELLER_ADDRESS ) as Seller,

count (distinct TOKENID) as NFT_IDs,

SUM (PRICE_USD) AS USD,

Median (PRICE_USD) AS avg_USD

FROM ethereum.core.ez_nft_sales

WHERE BLOCK_TIMESTAMP >= current_date - 180

and PROJECT_NAME in ('art blocks','parallel','doodles')

group by 1,2

concat ('Solana: ',CONTRACT_NAME) as Project,

DATE_TRUNC('week',BLOCK_TIMESTAMP) AS DATE,

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

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 >= current_date - 180

and CONTRACT_NAME in ('ABC','Okay Bears','Primates')

GROUP BY 1,2