with marketplaces as

(select marketplace ||'🌕' as marketplace,

count (distinct tx_id) as transactions,

count (distinct purchaser) as Buyers,

count (distinct seller) as Sellers,

count (distinct mint) as token,

sum (sales_amount) as SOL,

sum (sales_amount*solprice) as USD

from solana.core.fact_nft_sales AS PARAMETERS INNER JOIN

(select block_timestamp::date as time, avg (swap_to_amount/swap_from_amount) as SOLprice

from solana.fact_swaps where swap_from_mint = 'So11111111111111111111111111111111111111112' and swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') and swap_to_amount > 0 and swap_from_amount > 0 and succeeded = 'TRUE' group by 1) AS PRICESS

on PARAMETERS.block_timestamp::date = PRICESS.time

where (marketplace ILIKE '%Coral Cube%' or marketplace ILIKE 'hyperspace') and succeeded = 'TRUE' GROUP BY 1)

select

marketplace ,

transactions ,

Buyers ,

Sellers ,

token ,

SOL ,

from marketplaces