select

BLOCK_TIMESTAMP::date date ,

avg( SWAP_TO_AMOUNT/SWAP_FROM_AMOUNT) price

from solana.core.fact_swaps

where SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112'-- SOL

and SWAP_TO_MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' --USDC

and SWAP_FROM_AMOUNT >0

group by 1 order by date

),

sales as (select BLOCK_TIMESTAMP::date daily ,

marketplace ,

count (DISTINCT TX_ID) num_sales,

sum (num_sales) over (partition by marketplace order by daily ) cum_sales ,

count (DISTINCT PURCHASER) num_purchasers,

count (DISTINCT SELLER) num_sellers,

count (DISTINCT MINT) num_NFTs,

sum (SALES_AMOUNT) sales_volume,

sum (sales_volume) over ( partition by marketplace order by daily) cum_volume,

sum (SALES_AMOUNT*price ) sales_volume_usd,

sum (sales_volume_usd ) over (partition by marketplace order by daily ) cum_volume_usd,

AVG (SALES_AMOUNT) daily_avg_volume,

AVG((SALES_AMOUNT*price)) daily_avg_volume_USD,

Max (SALES_AMOUNT) Max_sale_volume

from solana.core.fact_nft_sales join SOL_price on date = BLOCK_TIMESTAMP::date

where marketplace in ( 'hyperspace', 'coral cube' )

and succeeded = true

and BLOCK_TIMESTAMP::date >= CURRENT_DATE - 40

group by 1,2

)

select marketplace ,

sum (num_sales) Total_sales ,

sum (num_sellers) Total_sellers ,

sum (num_purchasers) Total_purchasers ,

sum (sales_volume_usd) Total_volume_usd ,

sum (sales_volume) Total_sales_volume ,