SELECT block_timestamp::date AS date,

avg (swap_to_amount/swap_from_amount) AS SOLprice

FROM solana.fact_swaps

WHERE swap_from_mint = 'So11111111111111111111111111111111111111112' --SOL

AND swap_to_mint IN ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT

AND swap_to_amount > 0

AND swap_from_amount > 0

AND succeeded = 'TRUE'

GROUP BY 1

),

seles AS (

SELECT date_trunc('day',block_timestamp) AS date1,

COUNT(DISTINCT tx_id )AS sale_count ,

COUNT(DISTINCT purchaser) AS unique_buyers ,

COUNT(DISTINCT seller) AS unique_sellers ,

sum(sales_amount * SOLprice ) AS volume ,

sum(sale_count) over (ORDER BY date1) AS cumu_sales,

sum(unique_buyers) over (ORDER BY date1) AS cumu_buyer,

sum(unique_sellers) over (ORDER BY date1) AS cumu_sellers,

sum(volume) over (ORDER BY date1) AS cumu_volume,

sale_count/datediff(day,'2022-09-22' , current_date-1) AS average_sale_day,

unique_buyers/datediff(day,'2022-09-22' , current_date-1) AS average_unique_buyers_day,

volume/datediff(day,'2022-09-22' , current_date-1) AS average_volume_day,

volume/sale_count AS "average selling price (ASP) ",

avg(sales_amount * SOLprice) as avg_volume,

avg(avg_volume) OVER (ORDER BY date1 ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS MA_weekly,

avg(avg_volume) OVER (ORDER BY date1 ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS MA_monthly

FROM solana.core.fact_nft_sales a

LEFT JOIN SOL_Price o ON date_trunc('day',a.block_timestamp) = o.date

WHERE a.block_timestamp::date >= '2022-09-22'

AND marketplace = 'coral cube'

AND succeeded = TRUE

GROUP BY 1

)