trunc(BLOCK_TIMESTAMP,'day') as day ,

count(DISTINCT tx_hash) as sales ,

count(DISTINCT buyer_address) as buyers,

count(DISTINCT seller_address) as sellers,

count(DISTINCT project_name) as collection,

sum(price_usd) as volume_usd,

avg(price_usd) as avg_volume_usd,

sum(sales) over (order by day asc) as cumulative_sales,

sum(volume_usd) over (order by day asc) as cumulative_volume,

sum(buyers) over (order by day asc) as cumulative_buyer

from ethereum.core.ez_nft_sales

where block_timestamp::date >= '2022-07-01'

and tx_hash != '0xc5531fa64ce1e5c609489e734325835471215ad5f8e0972c94b64d12242d97e4'

and tx_hash != '0x3b21c4489b5905e2073ce8bd62d7493c3664e801910c1eed7817132e00e07aef'

and EVENT_TYPE = 'sale'

and platform_name = 'opensea'

and price_usd > 0

group by 1,2)

,

sol_token as ( select trunc(block_timestamp,'day') as day,

(sum(SWAP_TO_AMOUNT)/sum(SWAP_FROM_AMOUNT)) as sol_price

from solana.core.fact_swaps

where SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112'

and SWAP_TO_MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and block_timestamp::date >= '2022-07-01'

group by 1)

,

sol_d as ( select 'Magic-Eden' as type,

trunc(BLOCK_TIMESTAMP,'day') as day ,

tx_id,

purchaser,

seller,

label,

sales_amount*sol_price as vol

from solana.core.fact_nft_sales a left outer join