select date_trunc('day',block_timestamp) as date,

sum(swap_to_amount)/sum(swap_from_amount) as SOL_price

from solana.core.fact_swaps

where swap_from_mint = 'So11111111111111111111111111111111111111112' -- SOL

and swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' -- USDC

and block_timestamp >= '2021-12-01'

and succeeded = TRUE

group by 1

),

sales_data as (

select block_timestamp::date as date,

purchaser,

tx_id,

sales_amount

from solana.core.fact_nft_sales

where marketplace in ('coral cube')

and succeeded = TRUE

)

select count(distinct purchaser) as total_unique_buyers,

count(distinct tx_id) as total_sales,

sum(sales_amount) as total_volume_in_sol,

sum(sales_amount*SOL_price) as total_volume_in_usd,

avg(sales_amount*SOL_price) average_sales_amount,

median(sales_amount*SOL_price) median_sales_amount,

min(sales_amount*SOL_price) min_sales_amount,

max(sales_amount*SOL_price) max_sales_amount

from sales_data

join sol_price_data using (date)