date_trunc('day', hour) AS DATE,

avg(price) AS price

FROM ethereum.core.fact_hourly_token_prices

WHERE token_address ilike '0xD31a59c85aE9D8edEFeC411D448f90841571b89c'

GROUP BY 1)

date_trunc('day', block_timestamp) AS DATE,

CASE WHEN MARKETPLACE = 'solanart' THEN 'Solanart'

WHEN MARKETPLACE ilike '%magic eden%' THEN 'Magic Eden' END AS Marketplace,

COUNT(DISTINCT tx_id) AS n_sales,

COUNT(DISTINCT purchaser) AS n_buyers,

COUNT(DISTINCT seller) AS n_sellers,

COUNT(DISTINCT MINT) AS n_nfts,

sum(SALES_AMOUNT*price) AS tot_volume_usd,

avg(SALES_AMOUNT*price) AS avg_volume_usd,

median(SALES_AMOUNT*price) AS med_volume_usd

FROM solana.core.fact_nft_sales JOIN price_table ON date_trunc('day', BLOCK_TIMESTAMP) = price_table.DATE

WHERE (MARKETPLACE = 'solanart' OR MARKETPLACE ilike '%magic eden%') AND SUCCEEDED = 'TRUE'

GROUP BY 1, 2

ORDER BY 1