price_t AS (

SELECT

recorded_hour::date as date1,

AVG(close) as price

FROM

solana.core.fact_token_prices_hourly

WHERE

SYMBOL = 'SOL'

GROUP BY

1

)

BLOCK_TIMESTAMP::date as date,

MARKETPLACE,

COUNT(distinct TX_ID) as "Number of Sales",

SUM(SALES_AMOUNT * price) as "NFT Volume in USD",

COUNT(distinct PURCHASER) as "Number of Buyers",

COUNT(distinct SELLER) as "Number of Sellers",

"NFT Volume in USD"/"Number of Sales" as "Average Price of Each NFT"

solana.core.fact_nft_sales x

JOIN price_t y ON x.BLOCK_TIMESTAMP::date = y.date1

SUCCEEDED = 'true' and BLOCK_TIMESTAMP >= '2023-03-06'

GROUP BY

1,

2

order by

1