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 date ,

project_name Collection,

count (DISTINCT tx_id) num_sales,

count (DISTINCT purchaser) num_purchasers ,

sum (sales_amount) sales_volume ,

sum (sales_amount * price ) sales_volume_usd

from solana.core.fact_nft_sales join solana.core.dim_nft_metadata using(mint)

join SOL_price on BLOCK_TIMESTAMP::date = date

where marketplace = 'coral cube'

and collection is not null

and BLOCK_TIMESTAMP::date >= CURRENT_DATE - 40

group by 1 ,2

)

select Collection ,

sum (num_sales) num_sales ,

sum (num_purchasers) num_purchasers ,

sum (sales_volume) volume ,

sum (sales_volume_usd) volume_usd

from sales

group by 1 order by volume_usd desc limit 10